Let's say I have a movie database with genres and I would like to show all movies excluding some specific genres. I have associations all set up and working just fine.
So to do this, i came across the gem named Squeel and wrote this piece of code
movies = Movie.joins(:movie_genres).approved
# This is just an array of id's
included_genres = params[:genres][:included_genres]
excluded_genres = params[:genres][:excluded_genres]
if included_genres
# This works just fine
movies = movies.where(movie_genres: {genre_id: included_genres})
# This works too but IMO MySQL IN operator is faster.
#movies = movies.where{movie_genres.genre_id.eq_any included_genres}
end
if excluded_genres
# Here is where the problems occurs, it generates perfectly nice SQL
# but for some reason it doesn't work as expected.
movies = movies.where{movie_genres.genre_id.not_eq_any excluded_genres}
# If we do it this way, it will only not match those movies
# that have all the selected genres at once, i need to match
# all movies that have atleast one of the inputted genres
#movies = movies.where("genre_id NOT IN (?)", excluded_genres)
#movies = movies.where{movie_genres.genre_id.not_eq_all excluded_genres}
end
movies = movies.group(:id)
So yeah, thanks to Squeel we get this SQL:
SELECT `movies`.* FROM `movies`
INNER JOIN `movie_genres` ON `movie_genres`.`movie_id` = `movies`.`id`
WHERE ((`movie_genres`.`genre_id` != 10 OR `movie_genres`.`genre_id` != 12))
GROUP BY `movies`.`id`;
That's without included genres as they work as expected.
So in result, the where condition looks like it doesn't even apply as i see no difference with or without it.
See screenshot:
Does anyone have any ideas why it doesn't works and where should i start digging?
All help is much appreciated.
Solved this problem by using named scope
scope :excluding_genres,
lambda { |genres| {
:conditions =>
['movies.id NOT IN
(SELECT movies.id FROM movies
LEFT JOIN movie_genres ON movies.id = movie_genres.movie_id
WHERE movie_genres.genre_id IN (?))', genres]
}}