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.