Subquery in order by clause
Description
Activity
Gavin King January 22, 2020 at 6:06 PM
Actually my comment is nonsense: the JPA spec doesn’t support subqueries in the select clause, but Hibernate 5 definitely does. So you can write your query like this:
select b, (select max(c.number) from b.chapters c) as chnum from Book b order by chnum
Or you can write it using the approach I suggested above:
select b, max(c.number) as chnum from Book b join b.chapters c group by b order by chnum
Gavin King January 22, 2020 at 4:32 PM
Wouldn’t you folks be better off using a join and a group by for this?
To be honest I’m much more bothered by the fact that we don’t have subqueries in the select clause in H5 than I am by them not being allowed in order by. Perhaps that’s something we can fix in H6 if it’s not already fixed.
Mateusz Pszczolka November 28, 2019 at 10:05 AM
I faced exactly same problem. Please consider reopen this.
Marco Janc September 24, 2019 at 10:44 AM
Why is this an unusual case? Show products ordered by last sale is a common requirement in ecommerce.
Please consider reopening this.
vikas verma December 7, 2017 at 11:50 AM
I faced this same problem and I cannot use native SQL because currently we are using Postgres and in future, we may shift to MySQL then it may break in future.
Please reopen it and I think it is a major one.
Hibernate does not seem to like subqueries in an order by clause, e.g,
from Concern concern
order by
(select min(history.date)
from ConcernHistory history
where history.concern.id = concern.id)
With the Classic parser you can work around this by putting raw SQL into the HQL (nasty hack), but the AST parser is more strict. Sure enough the grammar for order bys in hql-sql.g is
orderExprs
: expr ( ASCENDING | DESCENDING )? (orderExprs)?
;
If you change this to
orderExprs
: exprOrSubquery ( ASCENDING | DESCENDING )? (orderExprs)?
;
subqueries work in order bys, at least in the limited examples I've tried.