PirateApp
PirateApp

Reputation: 6260

How to seek/keyset pagination on alphabetical data

resources

enter image description here

resource_votes_aggregate which stores the number of likes per resource_id

enter image description here

enter image description here

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;

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

enter image description here

UPDATE 1

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

Upvotes: 0

Views: 740

Answers (1)

Thom Brown
Thom Brown

Reputation: 2039

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.

Upvotes: 1

Related Questions