Rails how to find record by association's ids contain array

Question

course has_many tags by has_and_belongs_to, now given two id of tags, [1, 2], how to find all courses that have those both two tags

Course.joins(:tags).where("tags.id IN (?)" [1, 2]) will return record that have one of tags, not what I wanted

# app/models/course.rb
has_and_belongs_to_many :tags



# app/models/tag.rb
has_and_belongs_to_many :courses

Answer 1

Since you're working with PostgreSQL, instead of using the IN operator you can use the ALL operator, like so:

Course.joins(:tags).where("tags.id = ALL (?)", [1, 2])

this should match all ids with an AND instead of an OR.

Answer 2

This is not a single request, but might still be as quick as other solutions, and can work for any arbitrary number of tags.

tag_ids = [123,456,789,876] #this will probably come from params
@tags = Tags.find(tag_ids)
course_ids = @tags.inject{|tag, next_tag| tag.course_ids & next_tag.course_ids} 
@courses = Course.find(course_ids)