Reputation: 6260
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
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
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
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