JPA JPQL: SELECT NEW with COUNT, GROUP BY and ORDER BY

Question

There are 2 tables / entities: AppleTree and Apples. An apple tree produces 0...n apples. Each apple is an entity / row of the second table and references the apple tree that has produced it (ManyToOne).

I want to generate a "high score" report on the most productive apple trees. It should be ordered by the COUNT column in descending order:

APPLE TREE | COUNT(A)
---------------------
10304      | 1000
72020      | 952
31167      | 800

In order to handle these results, I created a non-entity bean that combines an AppleTree object and a long value (for COUNT):

// constructor
public AppleStats (AppleTree at, long howManyApples) { ... }

I want to fetch rows of this combined type using JPQL. My approach is based on the SELECT NEW syntax:

SELECT NEW foo.bar.AppleStats ( a.appleTree, COUNT(a) AS c )
FROM Apples a
GROUP BY a.appleTree
ORDER BY c DESC

Unfortunately it produces quite a few error messages. I think one problem is the column alias for the COUNT value. I use it as I want to sort by this aggregate value. It means no difference whether I use "COUNT(a) AS c" or "COUNT(a) c". It says the arguments were not separated by comma. Moreover, the "expression is invalid, which means it does not follow the JPQL grammar". Finally it says No constructors can be found that match the argument types."

Is there a way to get my AppleStats result rows? Or do I have to regress to native queries?

Answer 1

Try this query:

SELECT NEW foo.bar.AppleStats(a.appleTree, COUNT(a.appleTree))
FROM Apples a
GROUP BY a.appleTree
ORDER BY COUNT(a.appleTree) DESC

Unfortunately I don't have a JPA platform to test it right now, but the above should fix the syntax problems of the original query. And don't worry about the fact that the COUNT() aggregate function appears twice, that's a pretty common idiom when writing queries and any decent optimizer should be able to handle it and perform the operation only once.