Hello
I need to select "movies not rated by Joe"; I have 3 tables: users, movies, ratings.
The SQL would look like this:
Code:
SELECT movies.* FROM movies LEFT JOIN (SELECT ratings.movie_id FROM ratings WHERE ratings.user_id = 123) AS userRatings ON movies.id = userRatings.movie_id WHERE userRatings.id IS NULL;
In HQL following query seems to be the solution:
Code:
from Movie as movie where movie.id not in (select rating.movie.id from Rating as rating where rating.user.id = 123)
Ofcourse this ends up with...
Code:
"...WHERE movie0_.id NOT IN (SELECT rating1_.movie_id..."
...in the generated SQL. Somehow I assume the "NOT IN (SELECT" is not exactly the most effective "join". Am I wrong?
I was not able to google-up (or find in Hibernate "testunit" directory) any HQL-example that would feature left join & subselect in one query; otherwise I would rather use something like:
Code:
from Movie as movie left join (from Rating as rating where rating.user.id = 123) where rating.id is null
...but I just can't make that working.
Can anyone help, plz?
Yaroukh
Login
FAQ
Search
