Cursor based pagination with ID and sorting with Boolean-CodePudding

Cursor based pagination was implemented by remembering the ID of the last item from the last result.

SELECT * FROM emojis WHERE id < $last_id ORDER BY id DESC LIMIT 10

And I wanted to sort it with is_animated = true first. So If I have:

| id    | is_animated   |
|-----  |-------------  |
| 100   | true          |
| 99    | false         |
| 98    | false         |
| 97    | true          |
| 96    | true          |

It should be ordered with id DESC, is_animated DESC and return the following result:

| id    | is_animated   |
|-----  |-------------  |
| 100   | true          |
| 97    | true          |
| 96    | true          |
| 99    | false         | <- But I will never get this,
| 98    | false         | <- and this because the IDs are greater than 96.

I don't know which field to use to be my cursor and what SQL to query the data since ordering by IDs is no longer reliable.

In the "How to implement relay style cursor based pagination in postgres?" post, a SQL condition was mentioned:

WHERE (`name` > $last_name OR (`name` = $last_name AND `id` > $last_id))
ORDER BY `name` ASC, `id` ASC
LIMIT 10

also the post "Pagination with Relative Cursors" from Shopify

WHERE (`title` > "Pants" OR  (`title` = "Pants" AND `id` > 2))
ORDER BY `title` ASC, `id` ASC
LIMIT 10

but they were used to sort with fields which are orderable (like alphabets and numerics).

How to use the similar conditions but sorting with booleans? And what parameters/arguments do I need to remember to be my cursor for querying the next page?

CodePudding user response：

To include the rows you want, you'd need:

WHERE is_animated < $last_is_animated OR id <= $last_id

But at this point, the optimization of the cursor-based pagination is lost. The OR expression is bound to result in a table-scan anyway.

By the way, if you want to sort first by is_animated, you'd need to list it first in the order:

ORDER BY is_animated DESC, id DESC

An alternative way to write this query could be:

(SELECT ... WHERE is_animated = true AND id < $last_id ORDER BY id DESC LIMIT 5)
UNION
(SELECT ... WHERE is_animated = false ORDER BY id DESC LIMIT 5)
ORDER BY is_animated DESC, id DESC LIMIT 5;

This way if there are fewer than 5 rows matched by the first subquery, it'll fill the gap with a few rows from the second subquery.

Your code would need to check the is_animated of the last row fetched. If it's still true, then repeat the query. If the last row's is_animated is false, then on subsequent pages, change the query to run to the following:

SELECT ... WHERE is_animated = false ORDER BY id DESC LIMIT 5;