Hi, I am trying to write an order by with a subquery for a field to order on. Here was my first try (gen'd SQL shown at bottom):
Code:
select t from Task as t order by coalesce(completed, '3000-01-01') desc, started desc, maxelement(updates.entered) desc
I didn't totally expect it to work, but it was close. The only problem was that it used the 'id' field of the Update instead of 'entered'. So here is my second try:
Code:
select t from Task as t order by coalesce(completed, '3000-01-01') desc, started desc, (select max(u.entered) from Update as u where u.task = t) desc
But this gives an exception and I think it's because I am using the subquery. Here's the exception details:
Code:
org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: query [select t from taskqueue.model.Task as t order by coalesce(completed, '3000-01-01') desc, started desc, (select max(u.entered) from taskqueue.model.Update as u where u.task = t) desc]
Any ideas what the right HQL is or how I can get around this? Thanks.
Details:
Hibernate version: 3.1.1
Name and version of the database you are using: MySQL 4.1
The generated SQL (show_sql=true):Code:
DEBUG (org.hibernate.SQL:346) - select task0_.id as id0_, task0_.name as name0_, task0_.description as descript3_0_, task0_.started as started0_, task0_.completed as completed0_ from tq_task task0_ order by coalesce(task0_.completed, '3000-01-01') desc, task0_.started desc, (select max(updates1_.id) from tq_update updates1_ where task0_.id=updates1_.task_id) desc
Login
FAQ
Search