Is it possible to write hql query to order by some subquery? There's query like this:
select a from A.a join a.b where a.b.id = :id
what I need is something like:
select a from A.a join a.b where a.b.id = :id order by (select count(c) from C c where c.a = a.id) desc
My aliasing system does not seem to be working. Or it may be a bug with hibernate, but this thing works(though very inefficient) and I have to hard code the alias
SELECT a, (SELECT COUNT(c) FROM C c WHERE c.a = a.id) AS col_1_0_
FROM A.a
JOIN a.b
WHERE a.b.id = :id
ORDER BY col_1_0_
I am using Hibernate 3 and MySQL