https://kotlinlang.org logo
#exposed
Title
# exposed
v

Viet Hoang

06/04/2020, 7:35 AM
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.
293 Views