Ruby on Rails - select where ALL ids in array

Question

I'm trying to find the cleanest way to select records based on its associations and a search array.

I have Recipes which have many Ingredients (through a join table) I have a search form field for an array of Ingredient.ids

To find any recipe which contains any of the ids in the search array, I can use

eg 1.

filtered_meals = Recipe.includes(:ingredients).where("ingredients.id" => ids)

BUT, I want to only match recipes where ALL of it's ingredients are found in the search array.

eg 2.

search_array = [1, 2, 3, 4, 5]
Recipe1 = [1, 4, 5, 6]
Recipe2 = [1, 3, 4]
# results => Recipe2

I am aware that I can use an each loop, something like this;

eg 3.

filtered_meals = []

 Recipes.each do |meal|
   meal_array = meal.ingredients.ids
   variable =  meal_array-search_array
     if variable.empty?
       filtered_meals.push(meal)
     end
   end
 end

 return filtered_meals

The problem here is pagination. In the first example I can use .limit() and .offset() to control how many results are shown, but in the third example I would need to add an extra counter, submit that with the results, and then on a page change, re-send the counter and use .drop(counter) on the each.do loop.

This seems way too long winded, is there any better way to do this??

Answer 1

Assuming you are using has_many through & recipe_id, ingredient_id combination are unique.

recipe_ids = RecipeIngredient.select(:recipe_id)
                             .where(ingredient_id: ids)
                             .group(:recipe_id)
                             .having("COUNT(*) >= ?", ids.length)
filtered_meals = Recipe.find recipe_ids

Answer 2

How about

filtered_meals = Recipe.joins(:ingredients)
                       .group(:recipe_id)
                       .order("ingredients.id ASC")
                       .having("array_agg(ingredients.id) = ?", ids)

You'll need to make sure your ids parameter is listed in ascending order so the order of the elements in the arrays will match too.