We are facing a problem that seems to be unsolvable in the current Hibernate implementation. Let’s say we have a query that is using aggregation and grouping:
select u.name, sum(o.amount) from UserEntity u join u.orders o where u.createdAt > ‘2018-01-01’ group by u.name
When we want to display the data in a UI grid, the obvious thing that comes into mind is to paginate the result (that can consist of thousands of rows). Pagination requires us to know the size of the result set, to calculate the amount of pages.
Normally, using native queries we would be able to to something similar to this:
select count(*) from (select u.name, sum(o.amount) from UserEntity u join u.orders o where u.createdAt > ‘2018-01-01’ group by u.name)
Unfortunately Hibernate doesn’t support subqueries in the from clause. Is there another way around this, except loading the entire result set to the memory and paginate on the application side?
Business requirements have ruled out infinite scrolling and using native query in that particular case.