Ruby On Rails 5, activerecord query where model association ids includes ALL ids in array

Question

I have a Has many belongs to association for Recipes and Ingredients.

I am trying to return recipes which have all the ingredient ids in a given array of integers. Eg. I search using multiple ingredients, and I want to return any Recipe that has all the ingredients. If too many ingredients are given, but the Recipe includes them all, it should also return the recipe.

I have tried a few things,

Recipe.joins(:ingredients).where(ingredients: { id: ids })

which returns the Recipes that have ANY of the ingredients

Recipe.joins(:ingredients).where('ingredients.id LIKE ALL ( array[?])', ids)

This gives an error: ERROR: operator does not exist: integer ~~ integer

How can I find only the recipes that include all of the ids given in the array?

Answer 1

Try this query:

# Ingredient ID array
ingredient_ids = [....]

Recipe.joins(:ingredients).where(ingredients: { id: ingredient_ids }).
  group("recipes.id").
  having('count(recipes.id) >= ?', ingredient_ids.size)

1. First line ensure that only recipes having any of the ingredients are fetched
2. Second & third lines ensure that loaded recipes have minimum ingredient size of the id array, so that if any of the ingredient is missing it won't be considered.

Hope it helps!