91–100 of 150 posts
What if your id column uses GUIDs?
How do you mean that it changes the proposition?
Cursor based pagination and offset base pagination are two different approach for different purpose (hence not interchangable). Cursor based is good when you know exactly where the next page go (Eg: chat message when you want to load previous messages). Offset based is good when you want to browse data randomly (Eg: you want to go to page 10 when you are in page 1)
It gives you a great overview on how things are generally priced etc if you order by price.
The Use The Index Luke material about this is essential background reading, but in terms of actually implementing: My favorite SQL implementation of cursor-based keyset pagination can be found on this Hasura issue: https://github.com/hasura/graphql-engine/issues/141 , specifically this comment: https://github.com/hasura/graphql-engine/issues/141#issuecom... . Something so elegant about this SQL. I enjoy it a lot. Als…
fyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]: > For row comparisons, (a, b) > (x, y) is equivalent to: > (a > x) OR ((a = x) AND (b > y)) [1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...
I wrote something similar, includind a playground which serves as a benchmark to compare the various pagination options:
Earlier quoted context omitted.
What if the last element on a page is deleted when you click "next"?
You use the sort key as the page selector. So if it's cities sorted by name, the query is "give me the first 20 cities with names after 'Constantinople'". If Constantinople gets the works, it doesn't matter, because there are still 20 cities whose names come after that lexicographically.
Relevant song: https://youtube.com/watch?v=Z0JhC3LO0-8
Earlier quoted context omitted.
fyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]: > For row comparisons, (a, b) > (x, y) is equivalent to: > (a > x) OR ((a = x) AND (b > y)) [1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...
Just in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like: SELECT * EXCEPT FOO_ID FROM FOO; I have always wanted this but have never vome across it...
There's many UX benefits for paginated queries. It gives customer a clear overview of how many products there are and customer can also navigate faster to get a better understanding of what general prices are. If the table/list doesn't have good filters, customer can easily act out binary search to find what they are looking for in an ordered list of items.
Same with highscores, forums threads and other similar things. Say you are browsing highscores and you want to see what scores are around 10000th position etc.
As a user I find having only prev/next buttons a bit claustrophobic in this case. I think UX should trump whatever performance gains there are from it.
This is trumpeted around and actually put into production every once in a while. The reason opaque pagination is an antipattern is because you can’t optimistically fetch resources. So your customer, the person that paying you for your product, needs to wait for some number of synchronous reads. With non-opaque offsets these can be done in parallel. If the typical request requires 4 pages, these can be done 4 at a tim…
As a user I find having only prev/next buttons a bit claustrophobic in this case. I think UX should trump whatever performance gains there are from it.
Earlier quoted context omitted.
> One way to get around this is to drop the notion of page number. Instead do greater than last element on last page. That's the whole point of a cursor, isn't it? And pagination just means querying the records to return a limited subset.
Depends how long the cursor is alive. Url containing ?date=lt:2012-12-27 is stateless, user readable and more likely to outlive ?cursorId=xyaxjeasu&page=2. Now it might not be as accurate as having a cursor but it is a lot easier to implement, and still beats simply ?page=2 without cursor, which is the level you see in most apis today.
The cursor is never invalidated. You can always get a valid response from any cursor, whether the resource exists or was already deleted. That's the whole point of using a cursor.
Perhaps your confusion lies in assuming that a cursor's life cycle is tied to a specific object. It isn't. A cursor means "get me whatever resources would immediately follow this resource". It matters nothing if the cursor exists or not. When you run the query, you will get exactly the resource which would follow the cursor. That's by design.
It has the potential to become complex, so I would assume it was an "also" capability, as opposed to an "instead of" capability.
I like the idea of a "range" syntax ("[X..Y], [X...Y], [XY], [XY], etc).
When I write APIs, server performance isn't really an issue for me. Most of the performance bottleneck, in my experience, is data transfer, so being able to optimize that, pays the greatest dividends.
Also, making the API easy to understand, and express semantically, is important.