I have a Rails project with a Product model and a Feature model and a many-to-many relationship between them as follows:
# == 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
# == 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
# == 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
I want to have a filter on the products that returns the products that have all of a list of features.
For example:
Product 1 has Feature 1, Feature 2 and Feature 3.
Product 2 has Feature 2, Feature 3 and Feature 4.
Product 3 has Feature 3, Feature 4 and Feature 5.
Product 4 has Feature 2 and Feature 5.
If the filter is given Feature 2 and Feature 3, it should return Product 1 and Product 2 but not Product 3 or Product 4.
The best that I've come up with so far is the following:
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
I'm not fond of this solution because it results in N+1 queries. How can I refactor the filter to get rid of the N+1 queries? The project is on Rails 6.0 and PostGres 12.
You can use a combination of joins and having to accomplish what you need.
Product.joins(:product_features)
.where(product_features: { feature_id: feature_ids_array })
.group('products.id')
.having('count(*) = ?', feature_ids_array.length)
For this to work properly, it is important to guarantee uniqueness of the product_id and feature_id combination in the ProductFeature model. This is best done on the database level by adding a unique index there:
add_index :product_features, [:product_id, :feature_id], unique: true