3 ms·
I tried to make cursor based pagination work in a GraphQL API over MySQL and failed. The problem was that it had to work for a very wide range of SQL statements
by hirundo 6y ago
I tried to make cursor based pagination work in a GraphQL API over MySQL and failed. The problem was that it had to work for a very wide range of SQL statements, with arbitrary order clause, where clause and at least one join.
Offset pagination works fine in this scenario. But something like `where id > 42 limit 100` fails with an arbitrary sorting order on non-unique columns. All I could do would be to generate the unpaginated result, then find the cursor within it, and take the limit after that. Which is too inefficient to be practical.
If I missed a decent solution, please tell.
- vbezhenar 6y agoYou sort by your fields and then by id. You also include those field values along with id into your “cursor”. This is complicated for general case but possible to implement.
- dudul 6y agoAll the columns you order by should be part of your cursor value, and the id can be used to tie break non unique columns.
- simonw 6y agoI implemented this for Datasette. The key is to use the primary key as a "tie breaker" - so you sort by the specified column first and then by the primary key. I managed to get this working with compound primary keys too: click "next" at the bottom of this page for a demo: https://latest.datasette.io/fixtures/compound_three_primary_keys https://latest.datasette.io/fixtures/compound_three_primary_... The code is pretty complicated - mostly here: https://github.com/simonw/datasette/blob/0.53/datasette/views/table.py#L535-L594 https://github.com/simonw/datasette/blob/0.53/datasette/view...