Rails 7多对多关系中，如何按多标识符条件查询Item模型？

模型背景 #

首先是三个关联模型的定义：

class Item < ApplicationRecord
  has_many :item_identifiers
  has_many :identifiers, through: :item_identifiers
end

class ItemIdentifier < ApplicationRecord
  belongs_to :item
  belongs_to :identifier
end

class Identifier < ApplicationRecord
  has_many :item_identifiers
  has_many :items, through: :item_identifiers
end

需求说明 #

现有一个Item关联了foo和bar两个标识符，同时有三个搜索数组：

search1 = ["oo"]
search2 = ["foo", "ar"]
search3 = ["fo", "baz"]

需要实现查询逻辑，让search1和search2能匹配到该Item，search3无法匹配——核心要求是：查找满足所有搜索条件的Item，每个搜索条件至少匹配一个关联的标识符。

解决方案 #

方案一：使用EXISTS子查询（推荐，性能更优） #

通过为每个搜索词添加EXISTS条件，确保当前Item至少存在一个标识符匹配该搜索词，最后去重避免重复结果：

class Item < ApplicationRecord
  # ... 原有关联代码 ...

  def self.with_all_matching_identifiers(search_terms)
    query = all
    search_terms.each do |term|
      query = query.where(
        Identifier.joins(:item_identifiers)
                  .where("item_identifiers.item_id = items.id")
                  .where("identifiers.desc LIKE ?", "%#{term}%")
                  .exists
      )
    end
    query.distinct
  end
end

使用方式：

Item.with_all_matching_identifiers(search1) # 返回目标Item
Item.with_all_matching_identifiers(search2) # 返回目标Item
Item.with_all_matching_identifiers(search3) # 无结果

逻辑说明：

• 对每个搜索词，生成一个子查询，检查当前Item是否有对应的标识符匹配；
• 只有所有搜索词的子查询都返回true时，该Item才会被保留；
• distinct用于避免多次JOIN导致的重复结果。

方案二：使用分组+Having子句 #

通过分组统计每个Item匹配的搜索条件数量，确保数量等于搜索词的总数：

class Item < ApplicationRecord
  # ... 原有关联代码 ...

  def self.with_all_matching_identifiers(search_terms)
    joins(:identifiers)
      .group("items.id")
      .having(
        # 为每个搜索词生成一个判断条件：至少有一个标识符匹配该词
        search_terms.map { |term| "SUM(CASE WHEN identifiers.desc LIKE ? THEN 1 ELSE 0 END) > 0" }.join(" AND "),
        # 传递所有搜索词的占位符参数
        *search_terms.map { |t| "%#{t}%" }
      )
  end
end

逻辑说明：

• 先关联Item和标识符；
• 按Item分组后，对每个搜索词统计匹配的标识符数量，只要数量大于0就说明该条件满足；
• 只有所有条件都满足的分组才会被保留。

两种方案对比 #

• EXISTS方案：数据库可以提前终止每个子查询的查找（找到第一个匹配的标识符就停止），在数据量较大时性能更优；
• 分组方案：逻辑相对直观，但需要处理所有关联的标识符，数据量大时性能可能不如前者。

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