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?

That's what I'll be trying on Monday morning. I'm also considering going underneath JPA and doing the query in SQL, where it's very easy to express. — Adrian Cox, Apr 01 '12 at 11:01
I think I need to reach an equivalent of this SQL, probably using a subquery: `select sum(count) from (select count(*) from vehicle v group by v.make,v.model) as subquery;` I'm not sure this (or the even simpler `select count(distinct v.make || v.model) from vehicle v;` ) is expressible in JPQL. — Adrian Cox, Apr 01 '12 at 13:08
Just a historical note - JPA can't express what I needed, so I switched to SQL for the query. Not a QueryDSL problem. — Adrian Cox, Apr 20 '12 at 13:01
Adrian: your update would do an acceptable answer. What do you think? — Zecas, May 29 '12 at 15:15

score 3 · Answer 1 · answered Aug 11 '16 at 20:05

3

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

answered Aug 11 '16 at 20:05

Ali Saeed

1,519
1
16
23

2

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: http://stackoverflow.com/a/37784831/184998 – Adrian Cox Aug 14 '16 at 15:24

score 2 · Accepted Answer · answered Oct 20 '12 at 13:34

2

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

answered Oct 20 '12 at 13:34

Adrian Cox

6,204
5
41
68

How to count distinct items on specific fields in QueryDSL

2 Answers2