CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-6
Colibri
Colibri

Reputation: 1195

How to find a record through an association?

There are four tables - chat_rooms, chat_messages, chat_rooms_and_users and users:

Each room can have two users.

How to find a room knowing two users?

I tried like this:

room = joins(:chat_rooms_and_users)
       .find_by(
         type: ChatRoom.types[:private],
         chat_rooms_and_users: {
           user: [user_a, user_b]
         }
       )

SELECT "chat_rooms".* FROM "chat_rooms" INNER JOIN "chat_rooms_and_users" ON "chat_rooms_and_users"."room_id" = "chat_rooms"."id" WHERE "chat_rooms"."type" = $1 AND "chat_rooms_and_users"."user_id" IN ($2, $3) LIMIT $4  [["type", 0], ["user_id", 497], ["user_id", 494], ["LIMIT", 1]]

This bothers me in the SQL code:

"chat_rooms_and_users"."room_id" = "chat_rooms"."id"

If there are no rooms, then the first room is created normally. But then there is always only the room whose ID is first than the rest.

Upvotes: 0

Views: 38

Answers (1)

engineersmnky
engineersmnky

Reputation: 29308

Your issue is "chat_rooms_and_users"."user_id" IN ($2, $3) will return all "private" rooms where either user is present.

Instead you want to find a chatroom where both are present. I would recommend making a scope for this

#assumed 
class User < ApplicationRecord
  has_many :chat_rooms_and_users
end

class ChatRoom < ApplicationRecord 
 
  scope :private_by_users, ->(user_a,user_b) { 
      where(type: ChatRoom.types[:private])
      .where(id: user_a.chat_rooms_and_users.select(:chat_room_id))
      .where(id: user_b.chat_rooms_and_users.select(:chat_room_id))
   }
end 

#Then 
ChatRoom.private_by_users(user_a,user_b)

This will return a collection of "private" rooms where user_a and user_b are both participants. The SQL will look akin to:

SELECT "chat_rooms".* 
FROM "chat_rooms" 
WHERE "chat_rooms"."type" = 0 AND 
 "chat_rooms"."id" IN ( 
    SELECT 
      "chat_rooms_and_users"."chat_room_id" 
    FROM  
      "chat_rooms_and_users"
    WHERE 
      "chat_rooms_and_users"."user_id" = user_a_id
  ) AND "chat_rooms"."id" IN ( 
    SELECT 
      "chat_rooms_and_users"."chat_room_id" 
    FROM  
      "chat_rooms_and_users"
    WHERE 
      "chat_rooms_and_users"."user_id" = user_b_id
  )

If you can guarantee there will only ever be 1 or 0 rooms, of this type, with both participants then you can add first to the end of this chain.

Upvotes: 1

Related Questions