CODEWITHSUNDEEP
sqlactiverecord
Chris Bolton
Chris Bolton

Reputation: 2926

How to write / optimize this activerecord query?

Just getting started with ActiveRecord and tryin' to figure out how I'd go about doing a specific query. It'd be something equivalent to inputting a list of ingredients, and matching those to recipes those ingredients allow you to make. What would a good schema look like and how would something like this be done?

UPDATE:

So how I'm doing it so far is I have two models- ingredients and recipes, which are linked by a has_and_belongs_to_many relation. I'm using the following code to get the output I described, but it just seems too roundabout. If anyone could provide any insights on how to optimize / get this in a single query, would be great.

recipes = []
ingredients = ['i2', 'i1']
Recipe.uniq.joins(:ingredients).where("ingredients.name in (?)", ingredients).each do |recipe|
  included_ingredients = recipe.ingredients.take_while { |i| ingredients.include?(i.name) }
  recipes << recipe if recipe.ingredients.size == included_ingredients.size
end

Upvotes: 1

Views: 216

Answers (1)

aF.
aF.

Reputation: 66697

A good schema would be:

  1. Table recipe (recipe_id, recipe_name, etc.)
  2. Table ingredient (ingredient_id, ingredient_name, etc.)
  3. Table ingredients_recipes (recipe_id, ingredient_id)

The Table ingredients_recipes would be the connecting table.

Something like this should do the trick:

ingredients_recipes.joins(:recipe, :ingredient).where("ingredients_name = (?)", params[:ingredients])

This will join all tables and for a list of ingredients specified by an array.

For more info check THIS.

Upvotes: 1

Related Questions