I got a Product model with has_many Types table and several scopes:
class Product < ActiveRecord::Base
has_many :product_types
has_many :types, through: :product_types
scope :type1, -> { joins(:types).where(types: { name: "Type1" }) }
scope :type2, -> { joins(:types).where(types: { name: "Type2" }) }
end
When I try to use one scope (Product.type1 for example) all goes well, but two scopes at a time (Product.type1.type2) returns an empty query. Yes, one product may have multiple types.
Final goal is to create a filter of products by type with checkboxes form. When I check type1 and type2 I want to see all my products that have Type1 and Type1 at the same time.
UPD 1
So I've tried to do several queries and then & them as @aaron.v suggested. I wanted to do the logic inside of the function so:
def products_by_type_name(types)
all_types = types.each { |type| Product.joins(:types).where(types: { name: type }).distinct }
...
end
My point was to iterate through each type, collect all products and then & them inside the function. The problem is when I'm iterating, each loop returns string instead of array of hashes.
Product.joins(:types).where(types: { name: types }).distinct # returns array of hashes, it's okay.
types.each { |type| Product.joins(:types).where(types: { name: type }).distinct } # each loop returns string (Type name) instead of array of hashes.
What am I doing wrong?
SOLUTION 1
Suggested by @aaron.v, explained below
def self.by_type_name(types)
product_ids = []
types.each do |t|
product_ids << (joins(:types).where(types: { name: t }).distinct.select(:id).map(&:id))
end
find(product_ids.inject(:&))
end
SOLUTION 2
Found on reddit. In this function you are fetching all products with at least one required type and than grouping only ones that have required count of types. Thus, you get only those products that belongs to every type at the same time.
def self.by_type_name(types)
joins(:types).where(types: { name: types }).distinct.group('products.id').having('count(*) = ?', types.each.count)
end
If you have a database background, this would be pretty obvious as to why you wouldn't be able to find products with multiple types based off how you are writing your scopes.
Database queries that are written from these scopes will multiply the rows to ensure that a product that has many types, will have a distinct row for each type. Your query when you combine both scopes is writing
where `types`.name = "Type1" AND `types`.name = "Type2"
This will never happen since columns aren't added with multiples of the same row on a join.
What you want to do is have a method that you can pass an array of type names to find it
def self.by_type_name(types)
joins(:types).where(types: { name: types }).distinct
end
This method can accept one name by itself or an array of names for the types you want to find
If you pass an array of type names like ["type1", "type2"], this will result in a query like
where `types`.name in ("type1", "type2")
And then you should see the results you expect
UPDATE
To revise what you need in finding products that have all types given to your method, I decided to do this
def self.by_type_name(types)
product_ids = []
types.each do |t|
product_ids << (joins(:types).where(types: { name: t }).distinct.select(:id).map(&:id))
end
find(product_ids.inject(:&))
end
This method requires an array of type names(even if it is one type, pass an array with one type in it). What it will do is find all products with one specific type name, only select the product id(which will be lighter on your DB) and map it into an array which will get dumped into the product_ids array. after it has looped over each type name, it will find all products that intersect in each of the arrays resulting with products that have all types passed in.