Hibernate version: 3.0.5
Hi. I have a UserSubject class with a date and two many-to-one assotiation, user and subject. I'm trying to select only the latest rows for each subject and one given user, based on the date field.
I've got a query that works on MySQL 4.1 and gives me what I want, but can't port it to HQL.
MySQL query:
Code:
select us.* from user_subject us join
(select user_id, max(data) as data, subject_id
from user_subject group by user_id, subject_id) us2 on
(us.user_id = us2.user_id and us.data = us2.data);
HQL
Code:
List userSubjectList = getHibernateTemplate().find(
"select us from UserSubject us, "
+ "(select us2.user.id, max(us2.data) as data, us2.subject.id "
+ "from UserSubject group by us2.user.id, us2.subject.id) us2 "
+ "where (us.user.id = us2.user.id and su.data = su2.data) "
+ "and us.user.id = ? "
+ "order by us.subject.designacao", user.getId());
Hibernate gives me this:
Code:
unexpected token: ( near line 1, column 62 [select us from UserSubject
us, (select us2.user.id, max(us2.data) as data, us2.subject.id from
UserSubject group by us2.user.id, us2.subject.id) us2 where
(us.user.id = us2.user.id and su.data = su2.data) and us.user.id = ?
order by us.subject.designacao]; nested exception is
org.hibernate.hql.ast.QuerySyntaxError: unexpected token:
( near line 1, column 62 [select us from UserSubject us,
(select us2.user.id, max(us2.data) as data, us2.subject.id from
UserSubject group by us2.user.id, us2.subject.id) us2 where
(us.user.id = us2.user.id and su.data = su2.data)
and us.user.id = ? order by us.subject.designacao]
Based on what I have seen, Hibernate only accepts subselects on the where part of the query, is that the case? Is there any way I can rewrite this?
Thanks
Login
FAQ
Search