Keyset pagination

[ryanhiebert]

Does Hasura support Keyset pagination? It's basically cursor pagination, where the identifier for the last item in the sorted list is serialized and sent to the client so that the client can ask for another page starting from that spot.

[0x777]

Yes. An example:

query {
  articles (
    order_by: published_date_desc, 
    limit: 10, 
    where: { published_date: { _lt: previous_value } }
  ) {
    id
    title
    published_date
  }
}

You can use multiple columns too if published_date is not unique.

query {
  articles (
    order_by: [published_date_desc, id_desc], 
    limit: 10, 
    where: { published_date: { _lt: last_item_published_date } id: { _lt: last_item_id } }
  ) {
    id
    title
    published_date
  }
}

[ryanhiebert]

Thanks for the response. How does that where clause work? Does it join the conditions listed with AND, as I would think would be most intuitive to believe is the case from reading what you've written? That really isn't what I'm asking about.

What I'm looking for is actually row-level comparison across columns. For a more complicated example, consider a table that has three columns: pk, user, and seen (a timestamp). pk would be a completely unique identifier, but we'd want to sort by user, then seen, and finally, if necessary, by pk, which is known to be unique. The SQL query might look something like this:

SELECT pk, user, seen
FROM accesses
WHERE (user, seen, pk) > (
    SELECT user, seen, pk
    FROM accesses
    WHERE pk = 12345
)
ORDER BY user, seen, pk
LIMIT 100

The pk of 12345 would be a cursor-like marker of where you are in the pagination, because it's the id of the last item in the previous batch that you obtained. Then the fields are checked cumulatively together to start with the next item after the one that you saw, but without having to use OFFSET, which makes the query able to be significantly improved in the general case with a good index.

A row comparison like this:

WHERE (user, pk) > ('bob', 456)

Would be logically equivalent to

WHERE user > 'bob' OR user = 'bob' AND pk > 456

Not just

WHERE user > 'bob' and pk > 456

---

For further background on keyset pagination (and comparison with other pagination techniques), here's a few links:

https://use-the-index-luke.com/no-offset
http://allyouneedisbackend.com/blog/2017/09/24/the-sql-i-love-part-1-scanning-large-table/
https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

[0x777]

would be logically equivalent to
WHERE user > 'bob' OR user = 'bob' AND pk > 456

Oh ! Right. This can be expressed in where as follows:

{ 
  _or: [ 
    {user: {_gt: "bob" }}, 
    {user: {_eq: "bob"}, pk: { _gt: 456}}
  ] 
}

What I'm looking for is actually row-level comparison across columns.

There is no way to currently express comparisons like these WHERE (user, pk) > ('bob', 456) but you can express their equivalents WHERE user > 'bob' OR user = 'bob' AND pk > 456 as shown above. I can see it could get quite verbose when you add one more column. If we can think of a clean syntax for these expressions, it would be great !

Does this work?

[ryanhiebert]

Yes that is workable. Obviously not terribly beautiful, but workable.

AFA a nice syntax, I wonder if you already have some precedent for syntax that calls PG functions? The expression ('bob', 456) can also be written as ROW('bob', 456), which is useful in cases where you need to force a single thing to be a row instead of just assigning priority: ROW('bob') is different from ('bob'), IIUC.

---

Also embedded in my previous example was a subquery. Does Hasura support subqueries, such that if we figured out the function call syntax my whole use case of looking up the ordered field values for the particular pk would be possible?

[0x777]

We don't plan to add subqueries or function call syntax as of now. It'll be hard to reason about and enforce access control rules. You can still do the cursor based pagination as follows:

Create a view which gives us ordering information:

CREATE VIEW accesses_cursor AS 
SELECT
  l.pk as "access_id",
  r.pk as "after"
FROM
  accesses l
JOIN
  accesses r
ON
  ((l.user, l.seen, l.pk) > (r.user, r.seen, r.pk));

Now we add what we call an array relationship (a one to many relationship) named cursor on the accesses table.

query paginated_accesses {
  accesses (
    order_by: [user_asc, seen_asc, pk_asc]
    where: {cursor: {after: {_eq: 12345}}}
    limit: 100
  ){
    pk
    user
    seen
  }
}

where condition on an array relationship translates to any. The query is roughly translated into sql as follows

SELECT
  pk
  user
  seen
FROM
  accesses a
WHERE
  EXISTS (
    SELECT 1 FROM accesses_cursor c
    WHERE c.access_id = a.pk AND c.after = 12345
  )
ORDER BY (user, seen, pk)

[ryanhiebert]

Thanks. I'll have to take some time to digest that, thank you for your feedback.

[0x777]

@rikinsk should we add this to docs?

[dminkovsky]

Expanded in #3498