Rails ActiveRecord如何正确实现带ON子句多条件的LEFT OUTER JOIN查询

原写法的核心问题 #

• 你之前的写法把items表的过滤条件放到了WHERE子句，而非LEFT JOIN的ON子句，导致LEFT OUTER JOIN被隐式转换为INNER JOIN——所有没有关联item的语言（items列全为NULL的行）会被WHERE items.version_id = '1.0'过滤掉，最终返回空结果。
• 额外的Item.distinct.pluck(:language_id)会生成第二条独立查询，性能差且不符合单条查询的要求。
• 补充：原原生SQL实际遗漏了items.id IS NULL的条件，如果某个非'en'语言存在version_id='1.0'的关联item，原SQL会错误地将其返回，需要补充该条件才能匹配“未关联指定version的item”的需求。

推荐实现方案（按优先级排序） #

方案1：NOT EXISTS 子查询（性能最优，推荐） #

该方案避免JOIN全表，数据库一旦找到匹配的关联item就会停止扫描，尤其适合items表数据量大的场景，单条查询执行效率远高于LEFT JOIN写法。

封装为Scope（符合Rails最佳实践）

在Language模型中添加可复用的scope：

# language.rb
class Language < ApplicationRecord
  has_many :items

  # 筛选未关联指定version的item的语言
  scope :without_items_for_version, ->(version_id) {
    item_subquery = Item.where(
      Item.arel_table[:language_id].eq(arel_table[:id])
    ).where(version_id: version_id)
    where.not(item_subquery.exists)
  }

  # 排除指定id的语言
  scope :excluding_ids, ->(ids) {
    where.not(id: ids)
  }
end

调用方式

Language.without_items_for_version('1.0')
        .excluding_ids('en')
        .distinct
        .pluck(:name)

生成的SQL如下，完全匹配需求逻辑：

SELECT DISTINCT name FROM `languages`
WHERE NOT EXISTS (
  SELECT 1 FROM `items`
  WHERE `items`.`language_id` = `languages`.`id`
  AND `items`.`version_id` = '1.0'
)
AND `languages`.`id` != 'en'

方案2：带条件的LEFT JOIN（完全对应原SQL结构） #

如果希望更贴近原原生SQL的逻辑，可通过定义带条件的关联，将version_id的过滤条件放到ON子句中：

定义专属关联

# language.rb
class Language < ApplicationRecord
  has_many :items
  # 定义仅关联指定version的items的关联
  has_many :version_1_0_items, -> { where(version_id: '1.0') }, class_name: 'Item'
end

查询写法

Language.left_outer_joins(:version_1_0_items)
        .where(version_1_0_items: { id: nil }) # 筛选无关联item的语言
        .excluding_ids('en')
        .distinct
        .pluck(:name)

生成的SQL结构和原原生SQL完全一致，额外补充了items.id IS NULL的条件以匹配需求：

SELECT DISTINCT `languages`.`name`
FROM `languages`
LEFT OUTER JOIN `items` ON `items`.`language_id` = `languages`.`id`
  AND `items`.`version_id` = '1.0'
WHERE `version_1_0_items`.`id` IS NULL
  AND `languages`.`id` != 'en'

验证结果 #

以你给出的测试场景为例：

• languages表包含id为en/fr/de的三条记录
• items表仅存在一条version_id='1.0'、关联语言为en的记录
  两种方案都会返回['French', 'German']，完全符合预期。

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