Are subqueries supported in HQL SELECT or ORDER BY clauses?
I have seen, and have successfully done, subqueries in the WHERE clause, such as this:
Code:
select ria.Employee
from EmployeeResearchItemAssociation as ria
where ria.ResearchItem = :researchItem
and ria.IsActive = :isActive"
and ria.Employee.Type != :employeeType
and (ria.QueueLimit -
(select count(rriqe.ID)
from ResearchRequestItemQueueEntry as rriqe"
where rriqe.User = ria.Employee and
rriqe.ResearchRequestItem.ResearchItem = ria.ResearchItem
and rriqe.DeletionDate = null
) > 0)
However, when I try to sort by that same calculation, I run into errors. I have tried putting the calculation directly into the ORDER BY clause, like this:
Code:
select ria.Employee
from EmployeeResearchItemAssociation as ria
where ria.ResearchItem = :researchItem
and ria.IsActive = :isActive"
and ria.Employee.Type != :employeeType
and (ria.QueueLimit -
(select count(rriqe.ID)
from ResearchRequestItemQueueEntry as rriqe"
where rriqe.User = ria.Employee and
rriqe.ResearchRequestItem.ResearchItem = ria.ResearchItem
and rriqe.DeletionDate = null
) > 0)
order by (select count(rriqe.ID)
from ResearchRequestItemQueueEntry as rriqe
where rriqe.User = ria.Employee and
rriqe.ResearchRequestItem.ResearchItem = ria.ResearchItem
and rriqe.DeletionDate = null
)
...but this put a "half-interpreted" version of the HQL into the query sent to the db, and of course failed. The class names referenced in the ORDER BY became fully qualified, but other than that, nothing else was interpreted there.
I have also tried putting a subquery into the SELECT clause, giving it an alias, and referencing the alias in the ORDER BY:
Code:
select ria.Employee,
(select count(rriqe.ID)
from ResearchRequestItemQueueEntry as rriqe
where rriqe.User = ria.Employee and
rriqe.ResearchRequestItem.ResearchItem = ria.ResearchItem
and rriqe.DeletionDate = null
) as theorder
from EmployeeResearchItemAssociation as ria
where ria.ResearchItem = :researchItem
and ria.IsActive = :isActive"
and ria.Employee.Type != :employeeType
and (ria.QueueLimit -
(select count(rriqe.ID)
from ResearchRequestItemQueueEntry as rriqe"
where rriqe.User = ria.Employee and
rriqe.ResearchRequestItem.ResearchItem = ria.ResearchItem
and rriqe.DeletionDate = null
) > 0)
order by theorder
...but this results in an error saying that I "aggregate function expected before (".
Can anyone shed some light on the right way to do this? Thanks very much!
Hibernate version: 1.2.0 beta3
Login
FAQ
Search
