Rails复杂has_many through关联下自定义where.missing查询方法

当前artist_masters关联使用了接收实例参数的lambda作用域，属于实例依赖关联：Rails执行where.missing时会通过批量LEFT JOIN生成查询，不会提前实例化单个Artist对象传入作用域块，因此会触发两个报错：

• 预加载实例依赖作用域不被支持
• 作用域内拿不到artist实例，访问artist.id时出现nil错误

把实例依赖的关联作用域改成基于Arel字段匹配的无参数作用域，改造后关联支持批量查询、预加载、where.missing调用，不需要额外写自定义逻辑。

改造后的模型代码如下：

class Artist < ApplicationRecord
  has_many :artist_masters, -> {
    artist_masters_table = ArtistMaster.arel_table
    artists_table = Artist.arel_table

    where(
      artist_masters_table[:artist_id].eq(artists_table[:id])
        .or(artist_masters_table[:composer_id].eq(artists_table[:id]))
        .or(artist_masters_table[:performer_id].eq(artists_table[:id]))
        .or(artist_masters_table[:conductor_id].eq(artists_table[:id]))
    )
  }
  has_many :masters, through: :artist_masters
end

class Master < ApplicationRecord
  has_many :artist_masters, dependent: :destroy
  has_many :artists, through: :artist_masters

  # 补充角色关联的匹配条件，避免查询结果错乱
  has_many :composers, -> {
    where(artist_masters: { composer_id: Artist.arel_table[:id] })
  }, class_name: 'Artist', through: :artist_masters

  has_many :performers, -> {
    where(artist_masters: { performer_id: Artist.arel_table[:id] })
  }, class_name: 'Artist', through: :artist_masters

  has_many :conductors, -> {
    where(artist_masters: { conductor_id: Artist.arel_table[:id] })
  }, class_name: 'Artist', through: :artist_masters
end

class ArtistMaster < ApplicationRecord
  belongs_to :master
  belongs_to :artist, optional: true
  belongs_to :composer, class_name: 'Artist', optional: true
  belongs_to :performer, class_name: 'Artist', optional: true
  belongs_to :conductor, class_name: 'Artist', optional: true
end

改造完成后直接调用原生方法即可得到符合要求的结果：

# 返回所有未在artist_masters四个外键字段中出现过的Artist记录
Artist.where.missing(:artist_masters)
# 等价于返回没有关联任何Master的Artist记录
Artist.where.missing(:masters)

如果不想调整现有关联逻辑避免影响线上业务，可以直接手写LEFT JOIN查询实现和where.missing完全一致的效果：

Artist.left_joins(
  %{
    LEFT JOIN artist_masters
      ON artist_masters.artist_id = artists.id
      OR artist_masters.composer_id = artists.id
      OR artist_masters.performer_id = artists.id
      OR artist_masters.conductor_id = artists.id
  }
).where(artist_masters: { id: nil })

该查询的逻辑和需求完全匹配：筛选所有在artist_masters表的四个外键字段中都没有对应ID记录的Artist数据。

内容的提问来源于stack exchange，提问作者Pseudo-Hippy