Can not get total result count in a paginated query

Couchbase Server
1

Hi, I have a query that has pagination with LIMIT and OFFSET. But the problem is, I can not set the COUNT of overall result in a new field called “total” avoiding LIMIT and OFFSET

I want this

{
“total”: // total number of results or rows avoiding limit and offset
“results”: [
// this contains the final result rows with limit and offset
]
}

for example, the query for “results” will be like this: SELECT results FROM test as results where results.user_id = “4545” LIMIT 5 OFFSET 0

This will return 5 rows in page 1 (or offset 0) from total 50 rows. So the total will be 50, but “results” will contain only 5 rows due to LIMIT

Now I want to add the “total” field as well to this query which will show the overall result count avoiding LIMIT and OFFSET

How can I do this? Thanks

2

Issue two queries and combine the results.

SELECT  count(1) FROM `test` as `results` where `results` .user_id = “4545”

SELECT  `results`  FROM  `test`  as  `results` where  `results` .user_id = “4545” LIMIT 5 OFFSET 0;

OR

SELECT total, results 
    LET total = (SELECT  RAW count(1) FROM `test` as `results` where `results` .user_id = "4545")[0],
             results = (SELECT RAW r FROM test as r where r.user_id = "4545" LIMIT 5 OFFSET 0);

Pagination queries may not have count because it will be optimized may terminate early once pagination requirements are satisfied.

3

There is some best pattern with the introduction of Commo Table Expression ?

4

If the query doesn’t depend on FROM/JOIN you should use CTE.
If repeated use same query USE CTE.
Also check Subquery handling and potential query rewrite