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.
Advertisement
Answer
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