Rails 5.2中实现双人共享直接对话会话的联合查询方法

问题分析 #

你当前的查询用了WHERE assignments.user_id IN (xxx, xxx)，这会返回只要包含其中任意一个用户的会话，所以才会出现单个用户参与的结果。要拿到两个用户共同参与的会话，必须确保会话同时关联这两个用户的Assignment记录。

修正后的查询方案 #

方案1：分组统计匹配用户数 #

通过GROUP BY会话ID，再用HAVING子句筛选出恰好关联两个目标用户的会话：

user_ids = ["cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb", "32117e53-9b2f-49c6-8cc8-3a9eb9003a2e"]

Conversation
  .where(entity_type: "conversation", entity_subtype: "direct")
  .joins(:assignments)
  .where(assignments: { user_id: user_ids })
  .group('conversations.id')
  .having('COUNT(DISTINCT assignments.user_id) = ?', user_ids.size)

• COUNT(DISTINCT assignments.user_id)：避免同一个用户被多次关联到会话的异常情况（比如重复创建Assignment）
• user_ids.size：动态适配用户数量，后续扩展到多用户场景也能直接复用

方案2：双重关联匹配用户 #

通过两次关联assignments表，分别匹配每个用户，强制会话同时存在两个用户的关联记录：

user1_id = "cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb"
user2_id = "32117e53-9b2f-49c6-8cc8-3a9eb9003a2e"

Conversation
  .where(entity_type: "conversation", entity_subtype: "direct")
  .joins(:assignments)
  .where(assignments: { user_id: user1_id })
  .joins('INNER JOIN assignments AS a2 ON a2.assignable_id = conversations.id AND a2.assignable_type = "Conversation"')
  .where(a2: { user_id: user2_id })

这种方式逻辑更直白，适合固定两个用户的场景。

完整逻辑：检查并自动创建会话 #

把查询和创建逻辑整合，确保不存在目标会话时自动生成：

def find_or_create_direct_conversation(user1, user2)
  user_ids = [user1.id, user2.id]
  conversation = Conversation
    .where(entity_type: "conversation", entity_subtype: "direct")
    .joins(:assignments)
    .where(assignments: { user_id: user_ids })
    .group('conversations.id')
    .having('COUNT(DISTINCT assignments.user_id) = ?', user_ids.size)
    .first

  unless conversation
    conversation = Conversation.create!(entity_type: "conversation", entity_subtype: "direct")
    Assignment.create!(user: user1, assignable: conversation)
    Assignment.create!(user: user2, assignable: conversation)
  end

  conversation
end

# 使用示例
user1 = User.find("cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb")
user2 = User.find("32117e53-9b2f-49c6-8cc8-3a9eb9003a2e")
direct_conversation = find_or_create_direct_conversation(user1, user2)

内容的提问来源于stack exchange，提问作者Mark Denn