CODEWITHSUNDEEP
sqlpostgresqlgraphqldatabase-cursor
Ari Seyhun
Ari Seyhun

Reputation: 12521

How to implement GraphQL cursor in SQL query with sort by

I have a database table reviews with id, rating where id is auto-incrementing and rating is an integer between 0 and 100.

I'm attempting to create cursor based pagination in a GraphQL API but am struggling to create the necessary queries for hasPreviousPage and hasNextPage.

Here is my data:

ID: 1, RATING: 50
ID: 2, RATING: 80
ID: 3, RATING: 20
ID: 4, RATING: 40
ID: 5, RATING: 60

Here's an example of a GQL query:

reviews(first: 3)

Which returns

ID: 1, RATING: 50
ID: 2, RATING: 80
ID: 3, RATING: 20

With pageInfo

hasPreviousPage: false
hasNextPage: true

The queries for pageInfo would be

hasPreviousPage = SELECT COUNT(*) > 0 FROM reviews WHERE id < 0;
hasNextPage     = SELECT COUNT(*) > 0 FROM reviews WHERE id > 3;

My issue comes when sorting by rating. Making a query similar to before:

reviews(sort: "rating", first: 3)

Which returns

ID: 3, RATING: 20
ID: 4, RATING: 40
ID: 1, RATING: 50

With pageInfo

hasPreviousPage: false
hasNextPage: true

But how can I create the queries for hasPreviousPage and hasNextPage like I did before?

hasPreviousPage = SELECT COUNT(*) > 0 FROM reviews WHERE ???
hasNextPage     = SELECT COUNT(*) > 0 FROM reviews WHERE ???

What should the WHERE clause be in this case? Does the query need to be a lot more complex with a sub query? I'm not sure what I'm missing.

Upvotes: 2

Views: 2034

Answers (1)

Attila Večerek
Attila Večerek

Reputation: 800

You don't actually need any DB queries for hasPreviousPage and hasNextPage. You need to apply the +2 trick to achieve this (given that you want to implement hasPreviousPage for both before and after cases).

Let's say you have this query:

// `after` should be URL-safe encoded
// `id` must have a monotonic sort order, a ULID is a fine choice for an id
// if ULID is not an option for some reason, chose a different column that has a monotonicity to it, e.g. `created_at`
reviews(first: 3, after: "id:12345;sort_cols:user_id")

What you want to do is to query for the first 5 reviews:

SELECT *
FROM reviews
WHERE id >= ?
ORDER BY ? ASC
LIMIT 5;

-- result: 12345, 12346, 12347, 12348, 12349
-- from the app return (after computing `pageInfo`): 12346, 12347, 12348

If the id of the first result matches the id in the cursor, i.e. 12345 it means there is a previous page. If there is a previous page and the number of returned rows is 4 or less, hasNextPage: false. If there is not a previous page and the number of returned rows is 4 or more, hasNextPage: true.

Before you return the results, make sure to filter out the item matching the cursor id (12345) and the one extra item if there is a next page.

Beware that the SQL has to be generated correctly. The query would change depending on the direction of the pagination (before vs. after). It also becomes more complex if you want to support range requests, i.e. ones where before and after are supplied at the same time.

Example:

reviews(first: 3, before: "id:12345;sort_col:user_id")

Here you want to use a descending order. Also, you need to filter on ids <= the id from your cursor.

SELECT *
FROM reviews
WHERE id <= ?
ORDER BY ? DESC
LIMIT 5;

-- result: 12345, 12344, 12343, 12342, 12341
-- from the app return (after computing `pageInfo`): 12344, 12343, 12342

Upvotes: 4

Related Questions