I want to translate this query in HQL:
Code:
SELECT t_user.user_id
, t_request.req_id
, t_cycle.cycle_id
, t_cycle.time
FROM t_user
INNER
JOIN t_request
ON t_request.user_id = t_user.user_id
INNER
JOIN t_cycle
ON t_cycle.req_id = t_request.req_id
AND t_cycle.time =
( SELECT MAX(c2.time)
FROM t_request AS r2
INNER
JOIN t_cycle c2
ON c2.req_id = r2.req_id
WHERE r2.user_id = t_user.user_id )
First I created a DetachedCriteria for the subquery:
Code:
DetachedCriteria.forClass(Request.class, "request2")
.createAlias("request2.cycles", "cycle2")
.add(Restrictions.eqProperty("request2.user.userId", "user.userId"))
.setProjection(Property.forName("cycle2.time").max() )
that works. I can test by replacing user.userId with a value(
Restrictions.eq("request2.userId", new Long(1))) and I get:
Code:
SELECT MAX(c2.time) FROM t_request AS r2
INNER JOIN t_cycle c2 O ON c2.req_id = r2.req_id
WHERE r2.user_id = ?
Then I want to create the big query:
Code:
createCriteria(User.class)
.createAlias("user.requests", "request")
.createAlias("request.cycles", "cycle");
.add( Property.forName("cycle.time").eq(detachedCriteria) );
but what I get is wrong:
Code:
...
AND t_cycle.time =
(select max(t_cycle2.time) as y0_
from t_request2 where t_request2.user_id=?)
So as you see the inner join with the "t_cycle" table from the detached criteria dissapeared. Why ? Can you help me? Many thanks.
Login
FAQ
Search