How can I implement a count-of-groups in QueryDSL (in Java)?
Background
I'm implementing a paged search on a table, where I want to group the results before returning them.
In addition to the usual LIMIT x OFFSET y query, I also want to receive the total row count.
This is the (simplified) SQL query for one page:
SELECT x, y, MAX(z)
FROM tasks
WHERE y > 10
GROUP BY x, y
LIMIT 10 OFFSET 0
To retrieve the number of rows, I tried to use a naive COUNT(*) instead of the x, y, MAX(z) like this:
SELECT COUNT(x)
FROM tasks
WHERE y > 10
GROUP BY x, y
Unfortunately, this doesn't produce one row with the number of groups returned by the previous query, but one row for each group, each with the number of rows which were grouped together – as usual for aggregation functions, COUNT changes its meaning when a GROUP BY is present. (At least in Postgresql, what we are using here.)
We can retrieve the total count by using the first SELECT statement as a subselect in a from clause:
SELECT COUNT(*)
FROM ( SELECT x, y, MAX(z)
FROM tasks
WHERE y > 10
GROUP BY x, y
)
Now the hard part: how can we do this in JPQL and/or QueryDSL?
The first two queries are were produced by code like this:
QTask qTask = QTask.task;
Expression<?>[] groupExps = { qTask.x, qTask.y };
Predicate predicate = qTask.y.gt(10);
List<Result> results = getQueryDSL().createQuery()
.from(qTask)
.where(predicate)
.groupBy(groupExps)
.offset(0)
.limit(10)
.list(ConstructorExpression.create(Result.class,
qTask.x
qTask.y,
qTask.z.max()))
Long total = getQuerydsl().createQuery()
.from(qTask)
.where(predicate)
.groupBy(groupExps)
.singleResult(qTask.x.count());
It looks like JPA/JPQL doesn't support subselects other than in WHERE or HAVING-clauses, i.e. they are not possible in the FROM clause. This seems to be the cause that QueryDSL for JPA doesn't support them either.
Is there a way to rewrite this statement, or somehow work around this limitation?
