Keyset pagination get the super last page and total record count with the same query-CodePudding

I have a table test

------------------------------
id  | date     | description
------------------------------
1   | 07/08/09 | the date no 1.
10  | 07/08/10 | the date no 2.
3   | 07/08/11 | the date no 3.
9   | 07/08/12 | the date no 4.
... | ...      | ...

and I know that I can select the first page (for keyset pagination) like this

SQL NO. 1

select t.id as id, t.date as record_date, t.description as description
from test t order by t.id LIMIT 2

need to save the last record (3 | 07/08/11 | the date no 3. ) to the cache for the next page lookup.

and the next page can be retrieved using

SQL NO. 2

select t.id as id, t.date as record_date, t.description as description
from test t where t.id > 3 order by t.id LIMIT 2

Having in mind that my pagination looks like this

| << | < | > | >> |

where

> next page

>> super last page

< previous page

<< super first page

How to select records for super last page?

How to select records for previous page?

How to select records for super first page?

Would it be possible with one query to select currently selected record count selected records total available records for that query?

CodePudding user response：

You can query the last page like this:

SELECT * FROM (
   SELECT t.id AS id,
          t.date AS record_date,
          t.description AS description
   FROM test t ORDER BY t.id DESC LIMIT 2
) AS subq
ORDER BY id;

The previous page is fetched with a similar query, only with an additional WHERE condition.

But there is no fast way to get the total row count; you would have to calculate and count the whole result. My advice is not to give the user the exact count, but to EXPLAIN the query and show PostgreSQL's row count estimate. You can read my article on the problems involved with counting.