undefined | Better HN

0 pointssimonw3y ago0 comments

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

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.

0 comments

erikpukinskis3y 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?

simonwOP3y 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.

erikpukinskis3y 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?

j / k navigate · click thread line to collapse

0 pointssimonw3y ago0 comments

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

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.

0 comments

erikpukinskis3y 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?

simonwOP3y 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.

erikpukinskis3y 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?

j / k navigate · click thread line to collapse