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 ?

It should be easy enough to do the first one with

CustomFunction

but obviously that will be dialect specific

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

@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.

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