Rails Active Record：通过指向同一列的多自定义关联连接单表

我的Order模型定义了三个关联到Account表的自定义关联：

class Order < ApplicationRecord
  belongs_to :advertiser, class_name: 'Account'
  belongs_to :facilitator, class_name: 'Account'
  belongs_to :creator, class_name: 'Account'
end

我需要用Active Record和/或Arel生成如下SQL查询（不直接传入SQL字符串）：

INNER JOIN accounts ON accounts.id = orders.facilitator_id
OR accounts.id = orders.creator_id
OR accounts.id = orders.advertiser_id

我尝试过执行Order.joins(:advertiser, :facilitator, :creator)，但得到的是三次独立的INNER JOIN，结果如下：

SELECT `orders`.*
FROM `orders`
INNER JOIN `accounts` ON `accounts`.`id` = `orders`.`advertiser_id`
INNER JOIN `accounts` `facilitators_orders` ON `facilitators_orders`.`id` = `orders`.`facilitator_id`
INNER JOIN `accounts` `creators_orders` ON `creators_orders`.`id` = `orders`.`creator_id`

方法一：直接用Arel构建连接条件 #

通过Arel操作表和字段，精准构建带OR逻辑的连接条件：

orders_table = Order.arel_table
accounts_table = Account.arel_table

# 构建OR连接的匹配条件
join_condition = accounts_table[:id].eq(orders_table[:advertiser_id])
  .or(accounts_table[:id].eq(orders_table[:facilitator_id]))
  .or(accounts_table[:id].eq(orders_table[:creator_id]))

# 生成查询
orders_with_join = Order.joins(orders_table.join(accounts_table, Arel::Nodes::InnerJoin).on(join_condition).join_sources)

这段代码会生成你需要的单条INNER JOIN，用OR连接三个外键的匹配逻辑。

方法二：封装成模型Scope复用 #

如果需要重复使用这个查询逻辑，可以在Order模型里定义一个Scope：

class Order < ApplicationRecord
  belongs_to :advertiser, class_name: 'Account'
  belongs_to :facilitator, class_name: 'Account'
  belongs_to :creator, class_name: 'Account'

  scope :with_related_accounts, -> {
    orders_table = self.arel_table
    accounts_table = Account.arel_table
    join_condition = accounts_table[:id].eq(orders_table[:advertiser_id])
      .or(accounts_table[:id].eq(orders_table[:facilitator_id]))
      .or(accounts_table[:id].eq(orders_table[:creator_id]))
    joins(orders_table.join(accounts_table, Arel::Nodes::InnerJoin).on(join_condition).join_sources)
  }
end

之后直接调用Order.with_related_accounts就能生成目标SQL。

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