I have Rooms with a Many-To-Many relationship to SeminarLectures. I want to get all Rooms that have no lecture at a given date.
Why is this hibernate query not working?
SELECT r FROM Room as r
WHERE r NOT IN
(SELECT DISTINCT ls.rooms FROM SeminarLecture AS ls
WHERE ls.date <> :date)
I get:
Syntax Error in ...; expected "*, NOT, EXISTS, INTERSECTS, SELECT, FROM"
As documented here:
For databases that support subselects, Hibernate supports subqueries within queries. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed.
snippet:
from DomesticCat as cat
where cat.name not in (
select name.nickName from Name as name
)
so we would need to explicitly say what is NOT IN
// instead fo this
// WHERE r NOT IN
// we have to say what is not IN
WHERE r.id NOT IN
(SELECT DISTINCT ls.rooms FROM SeminarLecture AS ls
WHERE ls.date <> :date)
Other option would be to use NOT EXISTS (but then we should extend the inner WHERE clause with some match)
EXTEND:
Because we do face many-to-many relation, our HQL must be a bit more smarter, while resulting SQL statement will be a bit overkill. That's the side effect of many-to-many mapping, which I would suggest to avoid (see here)
So, Let's expect the entity model like this:
public class Room {
...
@ManyToMany(....
public Set<SeminarLecture> getSeminarLectures() {
return this.seminarLectures;
}
And also the inverse end:
public class SeminarLecture {
...
@ManyToMany(fetch = FetchType.LAZY, mappedBy = "seminarLectures")
public Set<Room> getRooms() {
return this.rooms;
}
Then the HQL we need would be like this:
SELECT r FROM Room as r
WHERE r.ID NOT IN
(SELECT DISTINCT r.ID
FROM SeminarLecture AS ls
JOIN ls.rooms r
WHERE ls.date <> :date)
That is it. We select the room ID from inner sub-query, and compare it with the same ID from the outer query.
My suggestion/way would be to avoid many-to-many. Explicit pairing table mapped as first level citizen will give us much more. We can introduce some more properties on this join table (Order, IsMain...). And mostly, the filtering will become much more simple and straightforward.
