Hi everyone, when handling a pagination query, to ...
# exposed
v
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
It should be easy enough to do the first one with
CustomFunction
but obviously that will be dialect specific
d
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
@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
Oh sorry, I misread the issue. Thanks for clarifying it for me.
576 Views