JPA/hibernate subquery in from clause

Question

We're using JPA with hibernate as the provider, we have a query that contains a join with a subquery in the FROM clause, but we get the following error:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 75 [SELECT sd FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates sd, (SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates x WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state GROUP BY x.viewId, x.state) sd2 WHERE sd.viewId = sd2.viewId AND sd.state = :state AND sd.changeDate = sd2.maxChangeDate]

This is the query:

SELECT sd  FROM SnapshotDates sd,       (SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state        FROM SnapshotDates x      WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state GROUP BY x.viewId, x.state) sd2 WHERE sd.viewId = sd2.viewId        AND sd.state = :state        AND sd.changeDate = sd2.maxChangeDate 

Thank you for helping

Answer 1

I did not think HQL could do subqueries in the from clause

https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-subqueries

note the sentence:

Note that HQL subqueries can occur only in the select or where clauses.

I imagine you could change it to a native query and execute it that way.

Answer 2

Your SQL is:

SELECT sd FROM SnapshotDates sd, (SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state FROM SnapshotDates x WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state GROUP BY x.viewId, x.state) sd2 WHERE sd.viewId = sd2.viewId AND sd.state = :state AND sd.changeDate = sd2.maxChangeDate

You can rewrite your sql like

SELECT sd  FROM SnapshotDates sd,  WHERE sd.viewId in (:viewIds)     AND sd.state = :state     sd.changeDate = (SELECT max(x.changeDate) FROM SnapshotDates x WHERE x.viewId = ds.viewId AND x.state = ds.state) 

Find inspired by example

SELECT m FROM Professor m WHERE (SELECT COUNT(e) FROM Professor e WHERE e.manager = m) > 0 

http://www.java2s.com/Code/Java/JPA/EJBQLWhereClauseWithSubQuery.htm

My similar example I had SQL

select k.* from kredits k,    (select client_id, max(r_date) r_date from kredits k group by client_id) k2  where k.client_id = k2.client_id      AND k.r_date = k2.r_date  order by k.id 

Rewrite it for PQL

select k From Kredit k where k.rDate = (select MAX(k2.rDate) from Kredit k2 where k2.clientId = k.clientId) order by k.id 

It will be translated to

select kredit0_.id as id28_, kredit0_.client_id as client59_28_ from kredits kredit0_  where kredit0_.r_date=(select MAX(kredit1_.r_date) from kredits kredit1_ where kredit1_.client_id=kredit0_.client_id)  order by kredit0_.id 

return same result as SQL.

Use Hebirnate 3.3.1 with MySQL 5.0.24