Can not get total result count in a paginated query

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

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;


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.

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

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