How to find a record having all required has_many associations in join table instead of OR / IN query

Question

I have a Restaurant Table, a Cuisine Table, and a join table connecting the Many To Many. I want to find a Restaurant that matches two specific cuisines, i.e., find the Restaurant that is listed in the join table with connections to Cuisine 1 and Cuisine 2.

I could hack something together with each and includes, but it feels like there must be some direct and obvious thing I'm missing in building my query using ActiveRecord.

Restaurant
ID| Name
1 | McDonalds
2 | Taco Bell

Cuisine
ID| Style
1 | American
2 | Mexican 
3 | Fast Food 

Restaurant_Cuisines
ID | Restaurant_ID | Cuisine_ID
1  | 1             | 1
2  | 1             | 3
3  | 2             | 2
4  | 2             | 3

I want to be able to query for restaurants that serve both American & Fast Food, which would give me back McDonalds but not Taco Bell as Taco Bell serves Fast Food but not American.

Answer 1

I can think of the following query which may not be most optimised solution but it gives correct answer and can guide in a direction to get optimised answer.

rest_ids = Restaurant_Cuisines.where(Cuisine_ID: 1).pluck(:Restaurant_ID) && Restaurant_Cuisines.where(Cuisine_ID: 3).pluck(:Restaurant_ID)
Restaurant.where(id: rest_ids)

If needs to be generalized:

def specific_restaurant(cuisine_ids)
  ids = cuisine_ids.map { |id| Restaurant_ID.where(Cuisine_ID: id).pluck(:Restaurant_ID) }.reduce(:&)
  Restaurant.where(id: ids) if ids.present?
end

Definitely N+1 where N is cuisine_ids but no harm if N is limited/few.

UPDATE - Finally, single query!

def specific_restaurant(cuisine_ids)
  ids = RestaurantCuisine.where(cuisine_id: cuisine_ids).group(:restaurant_id).having("count(*) = #{cuisine_ids.count}").pluck(:restaurant_id)
  Restaurant.where(id: ids) if ids.present?
end