ActiveRecord joins - Return only exact matches

Question

I have three models, Outfit, Product, and a join model OutfitProduct (Outfit has many Products through OutfitProducts).

I would like to find outfits that contain only exact product matches.

So far I have this

def by_exact_products(products)
  joins(outfit_products: :product)
    .where(outfit_products: { product: products })
    .group("outfits.id")
    .having('count(outfits.id) = ?', products.size)
end

The above returns any outfit that contains the products I am searching for, even if it is not an exact match. I would like it to return only outfits that are an exact match.

Example: Assume we have the following outfits made up of the following products:

outfit_1.products = [product_1, product_2, product_3, product_4]
outfit_2.products = [product_1, product_2]
outfit_3.products = [product_1, product_2, product_3]

If I passed [product_1, product_2, product_3] to my query, it will return outfit_1 and outfit_3 - I would like it to only return outfit_3 which is an exact match.

UPDATE (More info)

Calling the query with an array of three products produces the following query:

SELECT "outfits".* 
FROM   "outfits" 
       INNER JOIN "outfit_products" 
               ON "outfit_products"."outfit_id" = "outfits"."id" 
       INNER JOIN "products" 
               ON "products"."id" = "outfit_products"."product_id" 
WHERE  "outfit_products"."product_id" IN ( 18337, 6089, 6224 ) 
GROUP  BY outfits.id 
HAVING ( Count(outfits.id) = 3 )

user_3982562 · Answer

Let's first have a look at why this is happening. You use the following scenario:

outfit_1.products = [product_1, product_2, product_3, product_4]
outfit_2.products = [product_1, product_2]
outfit_3.products = [product_1, product_2, product_3]

This would have the following outfit_products table:

outfit_id | product_id
----------|-----------
        1 |          1
        1 |          2
        1 |          3
        1 |          4
        2 |          1
        2 |          2
        3 |          1
        3 |          2
        3 |          3

When you add the restriction:

WHERE "outfit_products"."product_id" IN ( 1, 2, 3 )

It will eliminate the row:

outfit_id | product_id
----------|-----------
        1 |          4

And leave product 1 with 3 records, when you group and count the records you'll end up with a resulting value of 3 for product 1. This means the current query will only check for a minimum of the provided products (aka make sure all the provided products are present).

To also eliminate records that have more products than the provided products you'll have to add a second count. Which counts the products without the above restriction.

def by_exact_products(products)
  # all outfits that have at least all products
  with_all_products = joins(outfit_products: :product)
                      .where(outfit_products: { product: products })
                      .group("outfits.id")
                      .having('count(outfits.id) = ?', products.size)

  # all outfits that have exactly all products
  joins(outfit_products: :product)
    .where(id: with_all_products.select(:id))
    .group("outfits.id")
    .having('count(outfits.id) = ?', products.size)
end

This will select all outfits that have have at least all provided products, and count their product total.

ActiveRecord joins - Return only exact matches

1 Answers