How can I count the number of result groups in Que

Question

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?

Answer 1

I can't help you out with jpa and jql, but it's an interesting SQL problem...

What RDMS? The below works in MS SQL Server. You can use concatenation, distinct and count to determine how many unique values there are when you concatenate x and y. This should be the same number of rows when you group by x,y.

select count(distinct CAST(x as char(10)) + CAST(y as char(10)))
from tasks;