Question 273083 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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 ?

Tags:

2

Answers


  1. 0

    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.
    Login or Signup to reply.
  2. 0

    I would do:

    class Room
  # ...
  has_many :users, though: :users_rooms
end

    Room.group(:id)
    .joins(:users)
    .where(users: { id: user_ids })
    .having(User.arel_table[:id].count.gte(user_ids.size))

    Creating the indirect assocation is optional but the abstraction makes it so that your query isn’t bound to how the join table is implemented.

    For example UsersRoom isn’t a great name and you might want to call it something more fitting like Booking – this lets you do that without breaking anything.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search