CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Michael Victor
Michael Victor

Reputation: 891

Filtering with Joins - Rails

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

Answers (3)

Nitin Srivastava
Nitin Srivastava

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

Michael Victor
Michael Victor

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

Qaisar Nadeem
Qaisar Nadeem

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

Related Questions