• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Tagn Sip
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic