7

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??

Rob Hughes
  • 876
  • 2
  • 13
  • 32
  • I've found a very similar question. And there is an answer that looks quite working https://stackoverflow.com/a/11512925/6229122. Isn't it? – Alex Suslyakov Dec 19 '17 at 15:43
  • If you only trouble is pagination in solution 3, you can look at Kaminari gem. `Kaminari.paginate_array(array_to_paginate).page(page_num).per(per_page)` – Hari Shankar Dec 19 '17 at 17:29

5 Answers5

2

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
Salil
  • 46,566
  • 21
  • 122
  • 156
  • Thanks for the response, but this wouldn't work as expected if providing a shed load of ingredients. Even in my Eg 2, this wouldn't work as the `search_array.length > Recipe.ingredient.length` . – Rob Hughes Dec 19 '17 at 12:50
1

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.

AndrewSwerlick
  • 913
  • 8
  • 24
0

Ruby on Rails Guide 2.3.3 - Subset Conditions

Recipe.all(:ingredients => { :id => search_array })

Should result in:

SELECT * FROM recipes WHERE (recipes.ingredients IN (1,2,3,4,5))

in SQL.

jon1467
  • 89
  • 2
  • 7
  • 1
    That's a link to an ancient guide. `all` with arguments won't work in newer versions. I would suggest: `Recipe.joins(:ingredients).where(ingredients: { id: search_array })` – lightalloy Dec 19 '17 at 12:00
  • 1
    Unfortunatly, this is like my Eg 1. As long as the Recipe ingredients contain ANY one of the searched ids, the result will show. – Rob Hughes Dec 19 '17 at 12:10
  • Ah, apologies to both of you, that was short-sighted of me. I'm actually quite surprised it works like that. It does seem like there isn't much choice, then. – jon1467 Dec 19 '17 at 12:25
0

Would the array & operator work for you here?

Something like:

search_array = [1, 2, 3, 4, 5]
recipe_1 = [1, 4, 5, 6]
recipe_2 = [1, 3, 4]

def contains_all_ingredients?(search_array, recipe)
  (search_array & recipe).sort == recipe.sort
end

contains_all_ingredients(search_array, recipe_1) #=> false
contains_all_ingredients(search_array, recipe_2) #=> true

This method compares the arrays and returns only the elements present in both, so if the result of the comparison equals the recipe array, all are present. (And obviously you could have a little refactor to have the method sit in the recipe model.)

You could then do:

Recipes.all.select { |recipe| contains_all_ingredients?(search_array, recipe) }

I'm not sure it passes your example three, but might help on your way? Let me know if that starts off OK, and I'll have more of a think in the meantime / if it's useful :)

SRack
  • 11,495
  • 5
  • 47
  • 60
0

I had a similar need and solved it using the pattern below. This is what the method looks like in my Recipe model.

  def self.user_has_all_ingredients(ingredient_ids)
    # casts ingredient_ids to postgres array syntax
    ingredient_ids = '{' + ingredient_ids.join(', ') + '}'
   
    return Recipe.joins(:ingredients)
                  .group(:id)
                  .having('array_agg(ingredients.id) <@ ?', ingredient_ids)
  end

This returns every recipe where all of the required ingredients are included in an ingredients array.

The Postgres '<@' operator was the magic solution. The array_agg function creates an array of each recipe's ingredient ids and then the left-pointing bird operator asks whether all of the unique ids in that array are contained in the array on the right.

Using the array_agg function required me to cast my search_array into Postgres syntax.

My Recipes model has many Ingredients through Portions.

I'd love to know if anyone has any better optimizations or knows how to avoid the casting to Postgres syntax that I needed to do.

baconsocrispy
  • 81
  • 2
  • 8