Hi everyone, when handling a pagination query, to get both the query result and the total number of ...

# exposed

[Viet Hoang]

Hi everyone, when handling a pagination query, to get both the query result and the total number of results before limit and offset is applied in 1 query, you have to use a window function:

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

or in case of MySQL, the FOUND_ROWS feature:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

Is there a way to achieve the same thing with Exposed ?

[spand]

It should be easy enough to do the first one with CustomFunction but obviously that will be dialect specific

[Dougy]

Sorry to be super late to the party. I was looking for the same thing and came across this convo thread. Not sure if it’s of any help now, being a month late, but there is a limit/offset feature:

https://github.com/JetBrains/Exposed/wiki/DSL#limit

[spand]

@Dougy The issue was about how to retrieve the total number of rows while only retrieving a subset. ie. getting the information to say “You are viewing page 50 to 100 of 42000” in a single query.

[Dougy]

Oh sorry, I misread the issue. Thanks for clarifying it for me.