How to count distinct items on specific fields in QueryDSL

Question

Edit: it turns out that JPA can't express this. The solution was to rewrite in SQL.

I'm using QueryDSL to perform an aggregate query on a JPA data set for reporting. I have no problem extracting the report data. For example:

...
query = query.groupBy(QVehicle.vehicle.make, QVehicle.vehicle.model);
return query.listDistinct(new QMakeModelReportData(
            QVehicle.vehicle.make, QVehicle.vehicle.model,
            QVehicle.vehicle.make.count()));

This produces a list of my DTO object, each of which contains a vehicle make, vehicle model, and the count of vehicles of that make model. Like this:

   Ford, Focus, 14
   Ford, Mondeo, 4
   Vauxhall, Astra, 4

But I can't work out the syntax to count the number of rows before I actually perform the query. The syntax I imagine is like this, which doesn't exist:

return query.countDistinct(QVehicle.vehicle.make, QVehicle.vehicle.model);

I've ended up with a rather inefficient option:

return query
    .listDistinct(QVehicle.vehicle.make, QVehicle.vehicle.model)
    .size();

Is there anything better?

Solution 1

This is not a QueryDSL limitation, but a JPA limitation. The solution was to rewrite in SQL.

Solution 2

You can do query.select(q.field.countDistinct())

Comments

[Adrian Cox]

I am attempting to count distinct on multiple fields, not a single field. JPA is limited to performing count distinct on a single field only: stackoverflow.com/a/37784831/184998