CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
ferne97
ferne97

Reputation: 1073

Filter by all ids in an array

I have two models, both with a has_and_belongs_to_many relationship.

class Movie < ApplicationRecord
  has_and_belongs_to_many :genres
end

class Genre < ApplicationRecord
  has_and_belongs_to_many :movies
end

So for example on the Movie index page, I want to be able to filter by Genre. For the filter form, I have a multi-select box of genres that when submitted sends an array of ids.

How would I find Movies that match ALL of those ids in the array instead of just any of the ids using Active Record? If a user selects for example, horror and comedy, it should filter down to movies that are both horror and comedy, not just any horror or any comedy.

ids = params[:genres]
Movie.includes(:genres).where(genres: {id: ids})

The above example finds any movie that is either horror or comedy.

Upvotes: 2

Views: 822

Answers (1)

ferne97
ferne97

Reputation: 1073

Figured it out by using joins with group and having.

ids = params[:genres]

Movie.joins(:genres)
  .where(genres: {id: ids})
  .group(:id)
  .having("count(*) = #{ids.count}")

This will basically filter down the duplicate matches, and return only the movies that have the same amount of results as the original array count. Meaning if the array count is 3, only movies that have 3 results from the original joins query will match.

Upvotes: 3

Related Questions