Reputation: 431
I have this table
users(
id uuid,
cpf string,
name string,
email string,
admin boolean
);
The following are indexed: cpf, name, admin.
When I first load this data from the API, it would be ordered by admin (admin users would be on top). However, since i'm using keyset pagination, I look for the last id in the data set and query again (where it has an X, it is dynamic):
SELECT id,
name,
cpf,
email,
Coalesce(admin, '0') as admin
FROM
users
WHERE
cpf >= '' (empty for the first query, then it would be the last one in the first data set)
AND (
name like 'X%'
OR
email like 'X%'
)
ORDER BY
X (admin, name, cpf)
ASC (or desc, depending on what was chosen)
LIMIT
X
So here I have my first data set, loaded fine, but when I would change the page, it would look for the cpfs that are bigger than the actual, they are not the actual next page. How would I fix this? Considering the user can choose wheter he order by: admin, name and cpf? The same column that orders need to be the row with the last row? And how this would work if I order by admin, since it is a boolean?
Upvotes: 1
Views: 1947
Reputation: 248305
For keyset pagination, if you want to order by (admin, name, cpf)
, the condition must be
WHERE (admin, name, cpf) > (max_admin, max_name, max_cpf)
where the max_*
values are the last values returned by the previous query.
Upvotes: 4