CODEWITHSUNDEEP
databasepostgresqlpagination
NataJdaCOliveira
NataJdaCOliveira

Reputation: 431

How to dynamically sort table data with keyset pagination?

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

Answers (1)

Laurenz Albe
Laurenz Albe

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

Related Questions