Reputation: 891
I have two models Product
and ProductProperties
. So, I store the properties for products in the Product Properties model which is associated with another model Properties
How can I implement a scope that finds a product with the properties (A or B or C) AND (X or Y or Z)
Filters I currently have are like so --
scope :product_type_filter, lambda {|property_id|
return nil if property_id.blank?
joins(:product_properties).where('product_properties.property_id IN (?)', property_id).distinct
}
scope :metal_filter, lambda {|property_id|
return nil if property_id.blank?
joins(:product_properties).where('product_properties.property_id IN (?)', property_id).distinct
}
And product the following SQL - SELECT DISTINCT "products".* FROM "products" INNER JOIN "product_properties" ON "product_properties"."product_id" = "products"."id" AND "product_properties"."deleted_at" IS NULL WHERE "products"."deleted_at" IS NULL AND (product_properties.property_id IN ('504')) AND (product_properties.property_id IN ('520'))
But it doesn't really work since it's looking for a Product Property which has both values 504 and 520, which will never exist.
Would appreciate some help!
Upvotes: 3
Views: 784
Reputation: 1424
Try this
scope :product_type_filter, ->(f_property_ids, s_property_ids) { joins(:product_properties).where('product_properties.property_id IN (?) AND product_properties.property_id IN (?)', f_property_ids, s_property_ids).distinct }
Call the scope
with 2 parameters
@products = Product.product_type_filter([1,2,3], [4,5,6])
Upvotes: 0
Reputation: 891
So this is the join that I used --
def self.find_with_properties property_ids, group_name
joins(:product_properties).joins('JOIN product_properties '+group_name+' ON '+group_name+'.product_id = products.id AND '+group_name+'.property_id IN ('+property_ids.to_s.tr('[', '').tr(']', '').tr('"', '') +')')
end
Upvotes: 1
Reputation: 2424
First of all I think you should not use scope for this task You should create a Class method to do it. It's best practice to not use scope when complex logic is involved.
Secondly it's nothing wrong the above mentioned code. It's about calling them wrong. I recon you are chaining above scopes.
By definition both above mentioned scope are same so you don't need to define twice. Try this one
def self.find_with_properties property_ids
joins(:product_properties).where('product_properties.property_id IN (?)', property_ids)
end
And Call it like
Product.find_with_properties([1,2,3]).find_with_properties([4,5,6]).uniq
If 'A' , 'B' , 'C'
are properties name then you should do it like:
self.find_by_properties_names(property_names)
self.joins(:product_properties=>[:property]).where("properties.name IN(?)",property_names)
end
Then can call like
Product.find_by_properties_names(["A","B","C"]).find_by_properties_names(["C","D","E"])
Upvotes: 0