JPA 2: In a query, how to compare the size of lists?

[Tagn Sip]

Hey all,

in a query, i wanna compare the size of two lists I have, lets say:

public class school{

List<pupil> pupils = new ArrayList();

}
....

And now i wanna get an descending ordered list of the schools in my DB, depending on their pupils-count( => size). I believe there was a way to get the size of a list in a query, but I'm not sure and i can't find a solution at the moment. I already tried the whole thing by using Group By on the schools and then order the schools by count(pupils)... but my DB(JDBC derby from NetBeans) doens't support aggregate functions in an Order By clause...

Thanks for help.

[James Sutherland]

Try any of,

--Using SIZE function (uses sub-select in SQL)

SELECT e FROM Employee ORDER BY SIZE(e.projects) DESC

--Using SIZE function, also selects the size (uses group by)

SELECT e, SIZE(e.projects) FROM Employee ORDER BY SIZE(e.projects) DESC

--Using GROUP BY

SELECT e, COUNT(p) FROM Employee JOIN e.projects p ORDER BY COUNT(p) DESC

--Using GROUP BY and alias

SELECT e, COUNT(p) AS pcount FROM Employee JOIN e.projects p ORDER BY pcount DESC

See,
http://en.wikibooks.org/wiki/Java_Persistence/Querying#How_to_order_by_the_size_of_a_collection