Are these possible in HQL?
I'll use the typical student - enrollment - course example to illustrate my situation.
Say i have table student which has a one to many relationship to enrollment which has a many to one relationship with course.
Using hibernates neat idbag feature, i hide the associative enrollment table so that each student has a List of courses.
I want all the students taking course 101 and 105 and 103 but not 106 or 107.
The list of required courses and excluded courses must be passed as paramters, and the size of the lists will vary every time.
After a bit of help on this board, i can do the first part of this query ( all the students taking 101 and 105 and 103) like so:
Code:
Integer[] ids = new Integer[]{new Integer( 101 ), new Integer( 103 ), new Integer( 105 )};
String queryString = "select s.id from Student as s join s.courses as c where c.id in (:idList) group by s.id having count(s.id) >= :listSize";
query = session.createQuery( queryString );
query.setParameterList("idList",ids);
query.setParameter("listSize",new Integer(ids.length));
however, im having trouble specifing the second criteria, that i dont want students who have enrolled in a seperate list of course ids.
using SQL i can do with with a conditional outer join like so:
Code:
select s.id,s.name
from
student as s
join enrollment as e1 on s.id=e1.student_id
left join enrollment as e2 on s.id = e2.student_id
and e2.dimension_keyword_id in (106,107)
where
e1.dimension_keyword_id in (101,103,105)
and e2.id is null
having count(e1.id) >=3;
Is there a way to do this in HQL? Hibernate doesn't seem to support conditions in it's join statement so im a bit lost. Perhaps some otherway to express the combination of must match, and must not match?
TIA,
dan.
Login
FAQ
Search
