Question 267688 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – pagination using WHERE and LIMIT

What to do in the case when it is impossible to apply "WHERE x < ..." or "WHERE x > ..." because the column "x" has more identical values than the LIMIT value
For example.
There is a table "payments_list". It has columns "id", "description", "cost", "payment_deadline".
LIMIT = 3
Our sorting by "payment_deadline" ASC and "cost" ASC
Let’s say we have a table:

id description cost payment_deadline payment_at
1 descr… 100 2023-10-01 2023-09-01
2 descr… 100 2023-10-01 2023-09-02
3 descr… 100 2023-10-01 2023-09-03
4 descr… 100 2023-10-13 2023-09-04
5 descr… 500 2023-10-18 2023-09-05
6 descr… 500 2023-10-18 2023-09-06
7 descr… 500 2023-10-18 2023-09-07
8 descr… 500 2023-10-18 2023-09-08
9 descr… 500 2023-10-18 2023-09-09
10 descr… 500 2023-10-20 2023-09-10

When going to a page with a list of applications, the request:

SELECT 
    *
FROM payments_list
ORDER BY payment_deadline, cost
LIMIT 3;

When scrolling the page down, 3 more requests should be loaded, the request should already take into account the sorting and the last values ​​from the loaded table:

SELECT 
    *
FROM payments_list
WHERE payment_deadline > 2023-10-01 AND cost > 100
ORDER BY payment_deadline, cost
LIMIT 3;

The second request will also work correctly, but the next request will already skip some records

I understand that, as an option for the last entry, you can use something completely unique, for example, the date the application was created, or “id” (we have it ordered – bigint), but the user page is not always sorted by “id”

While I was writing this message, it seems that I gradually began to understand that in the expression "WHERE ..." you also need to specify a unique field (for example, "id"), and then for "payment_deadline" and "cost" specify ">=" instead of "> ". Then the expression of the second query will be WHERE payment_deadline => 2023-10-01 AND cost => 100 AND id > 3

The second query should return:

id description cost payment_deadline payment_at
4 descr… 100 2023-10-13 2023-09-04
5 descr… 500 2023-10-18 2023-09-05
6 descr… 500 2023-10-18 2023-09-06

The third query should return:
| id | description | cost | payment_deadline | payment_at |
| ——– | ——– | ——– | ——– | ——– |
| 7 | descr… | 500 | 2023-10-18 | 2023-09-07 |
| 8 | descr… | 500 | 2023-10-18 | 2023-09-08 |
| 9 | descr… | 500 | 2023-10-18 | 2023-09-09 |

Tell me how to correctly compose a query in the case where there is multiple sorting

Tags:

2

Answers


  1. 0

    The solution usually is to add a unique key to the condition as a tie breaker. If the last page you read ended with the row that has id = 4, you fetch the next page with

    SELECT id, payment_deadline, ...
FROM payments_list
WHERE (payment_deadline, id) > ('2023-10-13', 4)
LIMIT 3;

    The last row you get will be the one with id = 7, so for the next page you continue with

    SELECT id, payment_deadline, ...
FROM payments_list
WHERE (payment_deadline, id) > ('2023-10-18', 7)
LIMIT 3;

    To make this fast, create a multi-column index on (payment_deadline, id).

    Login or Signup to reply.
  2. 0

    It is very important to know the specific database dialect you are working with

    Some clauses will work into a db2 database, but not on oracle, or mysql, or even a Microsoft Access database.

    That said, a generic approach that would work with most databases.

    So, for pagination you would need to provide in each query two fields:

    1. The date itself.
    2. The id as a tie breaker.

    The WHERE clause does accept compound statements.

    So, you could place a clause like

    WHERE ( (payment_deadline = :provided_date AND id > :provided_id ) OR (payment_deadline > :provided_date) ) AND ... your other clauses here ...

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search