4 ms·

Yah, another downside of cursor-based pagination is: what happens when the record the cursor refers to is deleted? Do you just crash and ask the user to start

Yah, another downside of cursor-based pagination is: what happens when the record the cursor refers to is deleted? Do you just crash and ask the user to start over? Do you have to nudge open cursors on every delete?

hamandcheese 4y ago
Instead of the cursor being an ID, it could directly encode whatever column(s) you are sorting by. Then you don’t have to locate any record in particular, you can always return records that sort after the cursor.
simonw 4y ago
My implementation of cursors works by encoding the primary ID of the last row on the page, along with additional information corresponding to the sort order if that's needed. That way it doesn't matter if the record is deleted - I can still return the next page by showing records that come after that provided cursor value. There's an example on this page: https://latest.datasette.io/fixtures/sortable?_sort=sortable https://latest.datasette.io/fixtures/sortable?_sort=sortable Since the table is sorted by the "sortable" column, the next page link includes this: ?_next=15%2Cg%2Cz 15 is the last value for "sortable" on the page, then g,z are the compound primary key for that last row.
- erikpukinskis 4y ago
  Interesting… what happens if new records are added with that 15 value? Do you need an implied secondary sort with the record created time? Also what if there are more than $page_size records with that 15 value?
  - simonw 4y ago
    Yes, if you want to support new records being added it's up to you to include something like a created date as part of your specified sort order. More than page_size records with that value works fine - that's why the primary key is included as a tie-breaker.
    - erikpukinskis 4y ago
      Doesn’t that take you back to the original problem though: if you delete the primary key the query fails? Would the created_at be a better option than the primary key? That can’t be deleted or changed, and that would give you a stable secondary sort?

hamandcheese 4y ago

Instead of the cursor being an ID, it could directly encode whatever column(s) you are sorting by. Then you don’t have to locate any record in particular, you can always return records that sort after the cursor.

simonw 4y ago

My implementation of cursors works by encoding the primary ID of the last row on the page, along with additional information corresponding to the sort order if that's needed. That way it doesn't matter if the record is deleted - I can still return the next page by showing records that come after that provided cursor value. There's an example on this page: https://latest.datasette.io/fixtures/sortable?_sort=sortable https://latest.datasette.io/fixtures/sortable?_sort=sortable Since the table is sorted by the "sortable" column, the next page link includes this: ?_next=15%2Cg%2Cz 15 is the last value for "sortable" on the page, then g,z are the compound primary key for that last row.

erikpukinskis 4y ago
Interesting… what happens if new records are added with that 15 value? Do you need an implied secondary sort with the record created time? Also what if there are more than $page_size records with that 15 value?
- simonw 4y ago
  Yes, if you want to support new records being added it's up to you to include something like a created date as part of your specified sort order. More than page_size records with that value works fine - that's why the primary key is included as a tie-breaker.
  - erikpukinskis 4y ago
    Doesn’t that take you back to the original problem though: if you delete the primary key the query fails? Would the created_at be a better option than the primary key? That can’t be deleted or changed, and that would give you a stable secondary sort?