3 ms·
You can do something like the following in application code. Imagine a table with the following schema/data: id=1,created_at='2022-28-05T18:00Z' id=2,created_a
by andrewmackrodt 4y ago
You can do something like the following in application code.
Imagine a table with the following schema/data:
id=1,created_at='2022-28-05T18:00Z'
id=2,created_at='2022-28-05T18:00Z'
id=3,created_at='2022-28-05T18:00Z'
To retrieve articles in order of descending creation time, our sort order would be:
created_at DESC, id DESC
The last item in the ORDER BY query should be a unique key to ensure we have consistent ordering across multiple queries. We must also ensure all supported sort columns have an INDEX for performance reasons.
Assume our application returns one article at a time, we have already retrieved the first article in the result set. Our cursor must include information for that last records ORDER BY values, e.g. serialize('2022-28-05T18:00Z,3'), for example purposes I will use base64, so our cursor is MjAyMi0yOC0wNVQxODowMFosMw==.
When the user requests the next set of results, they will supply the cursor and it will be used to construct a WHERE (AND) expression:
created_at < '2022-28-05T18:00Z' OR (created_at = '2022-28-05T18:00Z' AND id < 3)
So our query for the next set of results would be:
SELECT *
FROM articles
WHERE (created_at < '2022-28-05T18:00Z' OR (created_at = '2022-28-05T18:00Z' AND id < 3)
ORDER BY created_at DESC, id DESC
LIMIT 1
For queries with more than 2 sort columns the WHERE expression will slightly increase in complexity but it need only be implemented once. If the sort order direction is flipped, make sure to adjust the comparator appropriately, e.g. for 'DESC' use '<' and for 'ASC' use '>'.
- catlifeonmars 4y agoMakes total sense. It also seems trivially automatable on the surface. Echoing GP: Is this an existing DBMS feature? If not, why not?
- delusional 4y agoIsn't that basically just a trivial extension of what they article calls "keyset based pagination"? I'm not complaining since this is also what I usually do, I'm just wondering of theres more to it.
- cortesoft 4y agoOftentimes under the hood, cursor based paging IS keyset paging. The difference is that the keyset is obfuscated, which can allow the implementation to change without changing the API call.
- TravelPiglet 4y agoHow do you handle removal?
- andrewmackrodt 4y agoI haven't had a need to implement this but something like the following should work to provide the same* records regardless of insertion/deletion. * data of the record may have been updated, but if there are 1,000 records total, paginating in either direction will always return those same 1,000 records regardless of insertion/deletion. - Filter by created_at <= time the endpoint first returned a result. - Utilize soft deletes, where deleted_at IS NULL OR deleted_at < time the endpoint first returned a result. Any keys allowed to be used in the ORDER BY query should also be immutable.