ActiveRecord与查询

Question

我有三种模式:用户、会话和ConversationUser

class User < ApplicationRecord
  has_many :conversation_users, dependent: :destroy
  has_many :conversations, through: :conversation_users
end

class Conversation < ApplicationRecord
  has_many :conversation_users, dependent: :destroy
  has_many :users, through: :conversation_users
end

class ConversationUser < ApplicationRecord
  belongs_to :conversation
  belongs_to :user
end

我的目的

当两个用户有相同的会话时，我想找到一个会话(会话必须只有两个conversation_users，id为这两个用户)

用于测试的

我只创建了一个conversation和两个conversation_users

conversation = Conversation.create
conversation.conversation_users.create(user: User.first)
conversation.conversation_users.create(user: User.second)

我的问题

我想找到与的对话，是第一个用户和第二个用户。如果我执行以下查询，Active Record仍将显示会话，因为它正在执行OR子句，但我需要一个AND。正确的结果应该显示一个空数组，因为没有与User.first和User.third的对话。

Conversation.joins(:conversation_users).where(conversation_users: {user_id: [User.first.id, User.third.id]})

  User Load (3.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."created_at" ASC LIMIT $1  [["LIMIT", 1]]
  User Load (2.5ms)  SELECT "users".* FROM "users" ORDER BY "users"."created_at" ASC LIMIT $1 OFFSET $2  [["LIMIT", 1], ["OFFSET", 2]]
  Conversation Load (3.9ms)  SELECT "conversations".* FROM "conversations" INNER JOIN "conversation_users" ON "conversation_users"."conversation_id" = "conversations"."id" WHERE "conversation_users"."user_id" IN ($1, $2) LIMIT $3  [["user_id", "274d2f54-2418-4dec-a696-a5f62196ee85"], ["user_id", "0ef2f797-3518-4096-951b-1837ca28e4e4"], ["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Conversation id: "35556705-a162-4ee4-9776-963ae87bcfa8", created_at: "2020-04-20 09:34:05", updated_at: "2020-04-20 09:34:05">]>

Expectations

User.second

• Should和
• 应该找到与User.first的对话，而不是User.first和User.third

的对话

对于第一个期望，我尝试了这个查询，但是它返回了一个空数组

Conversation.joins(:conversation_users).where(conversation_users: {user_id: User.first.id}).where(conversation_users: {user_id: User.second.id})

  User Load (3.8ms)  SELECT "users".* FROM "users" ORDER BY "users"."created_at" ASC LIMIT $1  [["LIMIT", 1]]
  User Load (4.0ms)  SELECT "users".* FROM "users" ORDER BY "users"."created_at" ASC LIMIT $1 OFFSET $2  [["LIMIT", 1], ["OFFSET", 1]]
  Conversation Load (3.3ms)  SELECT "conversations".* FROM "conversations" INNER JOIN "conversation_users" ON "conversation_users"."conversation_id" = "conversations"."id" WHERE "conversation_users"."user_id" = $1 AND "conversation_users"."user_id" = $2 LIMIT $3  [["user_id", "274d2f54-2418-4dec-a696-a5f62196ee85"], ["user_id", "44a0c35a-b5c1-4bb6-a8a3-30ae3d4b3345"], ["LIMIT", 11]]
=> #<ActiveRecord::Relation []>

Answer 1

class Conversation < ApplicationRecord
  has_many :user_conversations
  has_many :users, through: :user_conversations

  def self.between(*users)
    users.map do |u|
      where("EXISTS(SELECT * FROM user_conversations uc WHERE uc.conversation_id = conversations.id AND uc.user_id = ?)", u)
    end.reduce(&:merge)
      .joins(:user_conversations)
      .group(:id)
      .having('count(*) = ?', users.length)
  end
end

这不是最干净的解决方案，但要做的是检查每个用户是否存在连接记录，然后.having('count(*) = ?', users.length)确保没有比用户更多的连接记录。

示例用法：

Conversation.between(User.first, User.second)
Conversation.between(1,2,3)
Conversation.between(*User.all) # splat it like a pro

这将导致以下SQL查询：

SELECT "conversations".* FROM "conversations" 
INNER JOIN "user_conversations" ON "user_conversations"."conversation_id" = "conversations"."id" 
WHERE 
  (EXISTS(SELECT * FROM user_conversations uc WHERE uc.conversation_id = conversations.id AND uc.user_id = 1))
  AND 
  (EXISTS(SELECT * FROM user_conversations uc WHERE uc.conversation_id = conversations.id AND uc.user_id = 2)) 
GROUP BY "conversations"."id"
HAVING (count(*) = 2) 
LIMIT $1

Answer 2

您可以使用AND条件执行如下查询

Conversation.joins(:conversation_users).where(conversation_users: {user_id: User.first.id}).where(conversation_users: {user_id: User.second.id})