mysqlruby-on-railsrubyactiverecordsqueel

Rails AR + Mysql - Excluding movie genres doesn't work as expected


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: Mysql terminal output showing work of sql query

Does anyone have any ideas why it doesn't works and where should i start digging?

All help is much appreciated.


Solution

  • 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] 
      }}