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:
Copy code
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:
Copy code
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 ?
s
spand
06/04/2020, 10:35 AM
It should be easy enough to do the first one with
CustomFunction
but obviously that will be dialect specific
d
Dougy
06/27/2020, 11:56 PM
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
s
spand
06/29/2020, 5:09 AM
@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.
d
Dougy
07/03/2020, 12:15 PM
Oh sorry, I misread the issue. Thanks for clarifying it for me.