How to seek/keyset pagination on alphabetical data

Question

• I have 2 tables

resources

resource_votes_aggregate which stores the number of likes per resource_id

• I want to paginate through resources in descending order of their title using seek/keyset pagination

• Without Pagination, this is what you get

• I want the results in batches of 5 as I am paginating 5 results at a time

My PAGE 1 query worked without issues using the query below

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
ORDER BY
   title DESC,
   resource_id DESC LIMIT 5;

• How do I get the next page using keyset/seek pagination?

My attempt

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
   AND 
   (
      likes,
      title
   )
   < (586, 'Zatoichi Meets Yojimbo (Zatôichi to Yôjinbô) (Zatôichi 20)') 
ORDER BY
   title DESC,
   resource_id DESC LIMIT 5;

Incorrect Results

UPDATE 1

I have created a FIDDLE HERE which loads 20 results? How to paginate this in batches of 5 using seek pagination?

Answer 1

There are two options that would do what you need:

OFFSET n FETCH FIRST n ROWS ONLY and sort_column > last_val

Example 1:

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
   AND 
   (
      likes,
      title
   )
   < (586, 'Zatoichi Meets Yojimbo (Zatôichi to Yôjinbô) (Zatôichi 20)') 
ORDER BY
   title DESC,
   resource_id DESC
OFFSET 5
FETCH FIRST 5 ROWS ONLY;

You would then just change the OFFSET value each time.

Then the other way would be to keep track of the title and resource_id in your application layer, and then pass this to the query so that you start further down the result set:

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes 
FROM
   resources r 
   LEFT JOIN
      resource_votes_aggregate a 
      ON r.resource_id = a.resource_id 
WHERE
   category_id = 1 
   AND 
   (
      likes,
      title
   )
   < (586, 'Zatoichi Meets Yojimbo (Zatôichi to Yôjinbô) (Zatôichi 20)')
   AND
     (title,resource_id) > (last_title_id_from_before,last_resource_id_from_before)
ORDER BY
   title DESC,
   resource_id DESC LIMIT 5;

This would ensure you skip rows from previous pages, but then downside with this strategy is you wouldn't have a way of going back a page again.