如何通过关联查找记录

Question

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

• chat_rooms-房间里有消息和用户
• 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比其他房间的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)

这将返回一个集合"；私人的"；CCD_ 8和CCD_ 9都是参与者的房间。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添加到这个链的末尾。