Rails多对多关联：按关联记录AND条件查询优化

项目环境与模型定义 #

基于Rails 6.0 + PostgreSQL 12，Product与Feature通过ProductFeature中间表实现多对多关联，模型代码如下：

Product模型 #

# == Schema Information
#
# Table name: products
#
#  id                                   :bigint           not null, primary key
#  created_at                           :datetime         not null
#  updated_at                           :datetime         not null
#
class Product < ApplicationRecord
  has_many :product_features, dependent: :destroy
  has_many :features, through: :product_features
end

Feature模型 #

# == Schema Information
#
# Table name: features
#
#  id                                   :bigint           not null, primary key
#  created_at                           :datetime         not null
#  updated_at                           :datetime         not null
#
class Feature < ApplicationRecord
  has_many :product_features, dependent: :destroy
  has_many :products, through: :product_features
end

ProductFeature中间表模型 #

# == Schema Information
#
# Table name: product_features
#
#  id            :bigint           not null, primary key
#  created_at    :datetime         not null
#  updated_at    :datetime         not null
#  product_id    :bigint           not null
#  feature_id    :bigint           not null
#
# Indexes
#
#  index_product_features_on_product_id  (product_id)
#  index_product_features_on_feature_id  (feature_id)
#
# Foreign Keys
#
#  fk_rails_...  (product_id => products.id)
#  fk_rails_...  (feature_id => features.id)
#
class ProductFeature < ApplicationRecord
  belongs_to :product
  belongs_to :feature
end

功能需求 #

实现产品筛选，返回包含所有指定Feature的产品。示例场景：

• Product 1：拥有Feature 1、2、3
• Product 2：拥有Feature 2、3、4
• Product 3：拥有Feature 3、4、5
• Product 4：拥有Feature 2、5

当筛选条件为Feature 2和3时，应返回Product 1和2，排除3和4。

当前实现的问题 #

现有方法会产生N+1查询（N为筛选特征的数量），每次循环都单独查询中间表，性能低效：

def filter_by_features(feature_ids_array)
  product_id_arrays = []
  feature_ids_array.each do |feature_id|
    product_id_arrays << ProductFeature.where(feature_id: feature_id).pluck(:product_id)
  end
  Product.where(id: product_id_arrays.inject(:&))
end

优化方案 #

以下两种方案均通过单/两次查询完成筛选，消除N+1问题，且充分利用现有索引：

方案1：关联查询+分组统计 #

def filter_by_features(feature_ids_array)
  Product.joins(:product_features)
         .where(product_features: { feature_id: feature_ids_array })
         .group('products.id')
         .having('COUNT(DISTINCT product_features.feature_id) = ?', feature_ids_array.size)
end

逻辑说明：

1. 通过joins关联Product与ProductFeature表
2. 筛选出关联指定特征ID的记录
3. 按产品ID分组，通过having约束分组内的不同特征ID数量等于筛选条件的数量，确保产品包含所有指定特征

方案2：子查询筛选产品ID #

def filter_by_features(feature_ids_array)
  Product.where(
    id: ProductFeature.select(:product_id)
                     .where(feature_id: feature_ids_array)
                     .group(:product_id)
                     .having('COUNT(DISTINCT feature_id) = ?', feature_ids_array.size)
  )
end

逻辑说明：

1. 子查询在ProductFeature表中筛选出关联所有指定特征的产品ID
2. 外层查询通过产品ID获取对应的Product记录
3. 此方案将逻辑合并为单次查询，PostgreSQL会自动优化执行计划

内容的提问来源于stack exchange，提问作者mark.sack