Query db for a model including related_model, where related model have 2 specific ids

In a rails app, i have a 3 models, Room, User and UsersRoom which is an association table between Room and User.
I want to query for a Room related to multiple users.

If i do:

Room.includes(:users_rooms).where(users_rooms: { user_id: user_ids }

I get all the room with at least one users_room containing one users_ids.
I want to get the room with users_rooms present for each user_ids.

Is there a clean way to do this without have to loop on the rooms after querying them ?

>Solution :

I would try the following:

Room
  .select('rooms.*, COUNT(users_rooms.user_id)')
  .joins(:users_rooms)
  .where(users_rooms: { user_id: user_ids })
  .group('rooms.id')
  .having('COUNT(users_rooms.user_id) >= ?', user_ids.size)

Notes:

• The COUNT in the select is required because some database engine versions complain when using COUNT in the having without having it defined in the select part. You can try to remove that line.
• The join does a INNER JOIN between both tables.
• The where is want you already had.
• The group groups the found records into a single line and allows using COUNT.
• The having only returns those rooms that had at least user_ids.size users_rooms associated.