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!

Answer

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