如何通过社团找到记录？

Question

有四个表- chat_rooms、chat_messages、chat_rooms_and_users和users

• chat_rooms -房间有消息和users.
• chat_rooms_and_users -通过这个表用户连接到房间。

每个房间可以有两个用户。

如何找到一个认识两个用户的房间？

我试过这样做：

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]]

这在SQL代码中困扰着我：

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

如果没有房间，那么第一个房间是正常创建的。但是，总是只有一个房间的ID是第一位的其他。

Answer 1

您的问题是，"chat_rooms_and_users"."user_id" IN ($2, $3)将返回所有的“私人”房间，任何一个用户都在场。

相反，你想找一个聊天室，两个人都在场。我建议你给这件事留个范围

#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)

这将返回一个集合的“私人”房间，其中user_a和user_b都是参与者。SQL看起来类似于：

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
  )

如果您可以保证只有1或0房间，这种类型的，有两个参与者，那么您可以添加first到这条链的末尾。