Open simonw opened 4 years ago
Yes, this would be nice!
I using Datasette v0.56 and don't see a previous page button.
I found one example of an implementation of reversed keyset pagination here: https://github.com/tvainika/objection-keyset-pagination/blob/cb21a493c96daa6e63c302efae6718d09aa11661/index.js#L74-L79
I think my ideal implementation for this would be to reverse the order, grab the previous page-size-plus-one items, then return a ?_next=x
token that would provide the previous page sorted back in the expected default order.
The alternative would be to have a ?_previous=x
token which can be used to paginate backwards in reverse order, but I think this would be confusing as it would result in "hit next page, then hit previous page" returning you to a new state which features rows in the reverse order.
Let's figure out the SQL for this. The most complex case is probably this one: https://latest.datasette.io/fixtures/compound_three_primary_keys?_next=a%2Ch%2Cr
select pk1, pk2, pk3, content from compound_three_primary_keys where ((pk1 > :p0)
or
(pk1 = :p0 and pk2 > :p1)
or
(pk1 = :p0 and pk2 = :p1 and pk3 > :p2)) order by pk1, pk2, pk3 limit 101
Where p0
is a
, p1
is h
and p2
is r
.
Given the above, how would I figure out the correct previous link? It should be https://latest.datasette.io/fixtures/compound_three_primary_keys?_next=a%2Cd%2Cv - a
, d
, v
.
I tried flipping the direction of the sort and the comparison operators and got this: https://latest.datasette.io/fixtures?sql=select+pk1%2C+pk2%2C+pk3%2C+content+from+compound_three_primary_keys+where+%28%28pk1+%3C+%3Ap0%29%0D%0A++or%0D%0A%28pk1+%3D+%3Ap0+and+pk2+%3C+%3Ap1%29%0D%0A++or%0D%0A%28pk1+%3D+%3Ap0+and+pk2+%3D+%3Ap1+and+pk3+%3C+%3Ap2%29%29+order+by+pk1+desc%2C+pk2+desc%2C+pk3+desc+limit+1+offset+99&p0=a&p1=h&p2=r
select pk1, pk2, pk3, content from compound_three_primary_keys where ((pk1 < :p0)
or
(pk1 = :p0 and pk2 < :p1)
or
(pk1 = :p0 and pk2 = :p1 and pk3 < :p2)) order by pk1 desc, pk2 desc, pk3 desc limit 1 offset 99
Which returned a-d-v
as desired. I messed around with it to find the limit 1 offset 99
values.
Same query again with a, d, v
returns 0 results, which is also as we would want: it signifies that we are back to the very first page: https://latest.datasette.io/fixtures?sql=select+pk1%2C+pk2%2C+pk3%2C+content+from+compound_three_primary_keys+where+%28%28pk1+%3C+%3Ap0%29%0D%0A++or%0D%0A%28pk1+%3D+%3Ap0+and+pk2+%3C+%3Ap1%29%0D%0A++or%0D%0A%28pk1+%3D+%3Ap0+and+pk2+%3D+%3Ap1+and+pk3+%3C+%3Ap2%29%29+order+by+pk1+desc%2C+pk2+desc%2C+pk3+desc+limit+1+offset+99&p0=a&p1=d&p2=v
Relevant code is some of the most complex in all of Datasette.
And
I'll need to think hard about how to refactor this out into something more understandable before implementing previous links.
I need this for
datasette-graphql
for full compatibility with the way Relay likes to paginate - using cursors for paginating backwards as well as for paginating forwards.