0
class Recipe < ActiveRecord::Base
  has_many :ingredients
end

class Recipe::Ingredient < ActiveRecord::Base
  belongs_to :product
  # also has `require_in_filter` boolean attribute
end

I want get all recipes by products ids where all these products are in ingredients with require_in_filter = true. For example, a recipe has 2 ingredients: both with require_in_filter = true and they are referencing products with ids of 2 and 3.

  • Query for recipes with products (2,3) MUST return this recipe.
  • Query for recipes with products (2,3,17) MUST NOT return this recipe.
  • Query for recipes with products (2) MUST NOT return this recipe.

What I tried so far:

Recipe.joins(:ingredients)
.where('recipe_ingredients.require_in_filter = ? AND recipe_ingredients.product_id IN (?)', true, [3,4])
.distinct

But this query returns recipes with ANY of the product ids (3 or 4 in this case). But I need only recipes with ALL of these products.

I found similar question PostgreSQL where all in array but doesn't help.

Recipe.joins(:ingredients)
.select('COUNT(*) AS count, recipes.*')
.where('recipe_ingredients.require_in_filter = ? AND recipe_ingredients.product_id IN (?)', true, [3,4])
.group('recipes.id').having('count(*) = ?', 2)

always return empty array

This raw sql

select recipes.*
from recipes 
join recipe_ingredients
on recipe_ingredients.recipe_id = recipes.id
where not exists(
    select * from recipe_ingredients ri 
    where ri.recipe_id = recipes.id
    and ri.product_id not in(2,3,17)        
)
and recipe_ingredients.require_in_filter = true
group by recipes.id

returns recipe with 2 and 3 as the only required products, but it shouldn't because 17 also must be required.

My SQL knowledge isn't sufficient to accomplish this task

Community
  • 1
  • 1
Oleg Antonyan
  • 2,943
  • 3
  • 28
  • 44

1 Answers1

0

Your second query doesn't work because you're GROUPing by recipes.id when – if I understand correctly – you want to query on the count of distinct product_id, so try GROUPing by that:

Recipe.joins(:ingredients)
.where('recipe_ingredients.require_in_filter = ? AND recipe_ingredients.product_id IN (?)', true, [3,4])
.group('recipe_ingredients.product_id').having('COUNT(*) = ?', 2)
eirikir
  • 3,802
  • 3
  • 21
  • 39
  • This gives an error: `PG::GroupingError: ERROR: column "recipes.id" must appear in the GROUP BY clause or be used in an aggregate function` – Oleg Antonyan Nov 06 '15 at 08:10