So I have product with multiple features, and features belong_to a feature_key and a feature_value.
Here is the query I'm running:
Product.family_features.joins(:feature_key, :feature_value)
.where(feature_keys: { name: ["Color", "Size"] }, feature_values: { name: ["Red", "Large"] })
.group_by(&:product_id)
Note: .family_features is a scope on Product that gets all the features related to the product's "family" -- which is a group of other products.
Now I want only one product that has a combination of features that have "color" => "red" and "size" => "large"
But I get features that have "color" => "red" and other sizes, as well as features with "size" => "large" and other colors.
Is there a way to constrain this query to the exact combination of values I need?
The relations look like this:
Product has_many :features
Feature belongs_to :product, :feature_key, :feature_value
FeatureKey has_many :features, :products (through features)
FeatureValue has_many :features, :products (through features)
You should be able to do this using arel.
fkt, fvt = FeatureKey.arel_table, FeatureValue.arel_table color_red = fkt[:name].eq('Color').and(fvt[:name].eq('Red')) size_large = fkt[:name].eq('Size').and(fvt[:name].eq('Large')) Feature.joins(:feature_key, :feature_value) .where(color_red.or(size_large)) .group(:product_id)
I have not tested the code, but I think it should work. Check the arel documentation for more details.
Note that I also changed the group_by to group. group_by is the Enumerable module Ruby method, while group is the ActiveRecord relation method performing an SQL GROUP BY, which should be much faster than grouping in Ruby.
Edit: Though your comment is saying that it worked, if you need all products that have size => large and color => red combination, I do not think this was what you were looking for.
You could try something like this:
red_products = Product.joins(features: [:feature_key, :feature_value]).where({feature_keys: { name: 'color' }, feature_values: { name: 'red' }})
large_products = Product.joins(features: [:feature_key, :feature_value]).where({feature_keys: { name: 'size' }, feature_values: { name: 'large' }})
products = red_products & large_products
上述解决方案的问题&在于Ruby中的数组交集，因此它将进行两个数据库查询，并与Ruby中的结果相交，这可能会成为大型数据集的问题。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句