Search code examples
mysqlsqldatabasepaginationhaving

Keyset pagination with WHERE & HAVING

I ran into a question that I can't find a solution yet for keyset pagination:

Suppose we have this query:

SELECT a.id, a.number, AVG(b.rating) AS rating 
FROM a LEFT JOIN b ON xxxxxxx
GROUP BY a.id
ORDER BY rating DESC, a.number DESC
LIMIT xxxx

How should we do keyset pagination on that?

For a.number only, we can have WHERE a.number < ?; For rating only, we do HAVING rating < ?; If there're two conditions for where, we can have WHERE (x, y) < (?, ?).

But how to do both HAVING and WHERE?

Edit: I implemented this today and found out that it's slower than OFFSET pagination, so not recommended.

Solution

  • The where you never can use, because it can'_t access rating.

    What you can do is

    HAVING (x, y) < (1, 1)

    SELECT a.id, a.number, AVG(b.rating) AS rating 
FROM a LEFT JOIN b ON a.id = b.id
GROUP BY a.id, a.number
HAVING (a.number, rating) < (1, 1)
ORDER BY rating DESC, a.number DESC
LIMIT xxxx