Basically I want to order a list of items by the size of a collection.
e.g. A has a one-to-many relationship with B
I want to retrieve a list of A ordered by the number of associated B's
I followed the instruction from the Hibernate User FAQ Tips and Tricks
http://www.hibernate.org/118.html#A5 but it does not appear to work.
HQL:
Code:
SELECT edge, size(target.outgoingEdgeList)
FROM Edge AS edge LEFT JOIN FETCH edge.target AS target
WHERE edge.source.id = :source.id
GROUP BY edge
this is the generated SQL:
Code:
select
edge.id as id,
edge.dateUpdated as dateUpda3_,
edge.name as name,
edge.dateDeleted as dateDele5_,
edge.dateCreated as dateCrea8_
from tb_edge edge inner join tb_vertex vertex0_ on edge.target_id=vertex0_.id
where this.source_id = ? group by edge.id
which generates the following error:
Code:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column 'edge.dateUpdated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.