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

Question

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

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

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

To process these results, I created a non-entity bean that combines the AppleTree object and the long value (for COUNT):

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

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

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

, . , COUNT. , . , "COUNT (a) AS c" "COUNT (a) c". , . , "" , JPQL ". , , , .

AppleStats? ?

Answer 1

:

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

, JPA , . , COUNT() , .