If I have a join model that joins two tables, how do I fetch all of the needed records in one query?-CodePudding

I have three tables recipes, ingredients, and recipe_ingredients (which joins the prior two)

In my recipe_controller I want to have my index method return all of needed data to show a recipe to the user.

The schema looks like (removed useless info):

"ingredients",
    t.string "name"
    t.string "food_group"
  end

"recipe_ingredients",
    t.bigint "recipe_id"
    t.bigint "ingredient_id"
    t.integer "quantity"
    t.string "measurement_unit"
  end

"recipes",
    t.string "name"
    t.string "genre"
    t.bigint "user_id"

I've tried something like

  def index
    recipe = Recipe.last
    recipe_ingredients = RecipeIngredient.where(recipe_id: recipe.id)
    ingredient_ids = recipe_ingredients.pluck(:id)
    ingredients = Ingredient.where('id in (?)', ingredient_ids)
    render json: { 
                  recipe: recipe, 
                  recipe_ingredients: recipe_ingredients,
                  ingredients: ingredients }
  end

to at least get one record correct, but I'm unsure where to begin when trying to create a general index method without writing a nasty, inefficient loop.

Also, I'm new to rails sql, and it is amazing, but I'm sure I'm missing a lot of optimization here so if there's something I can do to improve the above please let me know.

CodePudding user response：

I think the problem here is with your ingredients.

First of all you can get the recipe_ingredients like this

recipe_ingredients = recipe.recipe_ingredients

Other thing is you are not getting ids of ingredients but these are recipe_ingredients which is the bridge table.

ingredient_ids = recipe_ingredients.pluck(:ingredient_id)

Now your code should be like

def index
  recipe = Recipe.last
  recipe_ingredients = recipe.recipe_ingrediants
  ingredient_ids = recipe_ingredients.pluck(:ingredient_id)
  ingredients = Ingredient.where(id: ingredient_ids)
  render json: { 
              recipe: recipe, 
              recipe_ingredients: recipe_ingredients,
              ingredients: ingredients }

end

CodePudding user response：

You can create following association in your recipie model

has_many :recipe_ingredients
has_many :ingredients, through: :recipe_ingredients

And in you controller

def index
  @recipies = Recipie.includes(recipe_ingredients: :ingredients)
end

This will load all the recipies, recipe_ingredients and ingredients in single query. And in your view you can iterate on @recipies

@recipies.each do |recipie|
  recipe_ingredients = recipie.recipe_ingredients
  ingredients = recipie.ingredients
end

Update:

def index
  @recipies = Recipie.includes(recipe_ingredients: :ingredients)
  recipie_with_ingredients = @recipies.map do |recipie|
                               {
                                 recipie: recipie,
                                 recipe_ingredients: recipie.recipe_ingredients,
                                 ingredients: recipie.ingredients
                               }
                             end
render json: recipie_with_ingredients
end

CodePudding user response：

I'm gonna assume you don't actually want that JSON structure. Simply because it doesn't actually make sense given the overall goal. If you want to render a recipe you want to actually have quantity, name and unit together so that you don't have cobble the needed information together on the consuming end from two seperate arrays.

Ideally you would want JSON that resembles this:

{
  "name": "Chocolate Chip Cookies",
  "ingredients": [
    {
      "name": "Butter",
      "quantity": 100,
      "measurement_unit": "gr"
    },
    {
      "name": "Flour",
      "quantity": 300,
      "measurement_unit": "gr"
    }
  ]
}

Provided you have the following assocations:

class Recipe < ApplicationRecord
  has_many :recipe_ingredients
end

class RecipeIngredient < ApplicationRecord
  belongs_to :recipe
  belongs_to :ingredient
end

You can load the records off all three tables in a single query with:

@recipe = Recipe.eager_load(recipe_ingredients: :ingredient).last

This generates the following monster query:

SELECT
  "recipes"."id" AS t0_r0,
  "recipes"."name" AS t0_r1,
  "recipes"."created_at" AS t0_r2,
  "recipes"."updated_at" AS t0_r3,
  "recipe_ingredients"."id" AS t1_r0,
  "recipe_ingredients"."recipe_id" AS t1_r1,
  "recipe_ingredients"."ingredient_id" AS t1_r2,
  "recipe_ingredients"."quantity" AS t1_r3,
  "recipe_ingredients"."measurement_unit" AS t1_r4,
  "recipe_ingredients"."created_at" AS t1_r5,
  "recipe_ingredients"."updated_at" AS t1_r6,
  "ingredients"."id" AS t2_r0,
  "ingredients"."name" AS t2_r1,
  "ingredients"."created_at" AS t2_r2,
  "ingredients"."updated_at" AS t2_r3 
FROM
  "recipes" 
  LEFT OUTER JOIN
    "recipe_ingredients" 
    ON "recipe_ingredients"."recipe_id" = "recipes"."id" 
  LEFT OUTER JOIN
    "ingredients" 
    ON "ingredients"."id" = "recipe_ingredients"."ingredient_id"  
LIMIT 1

Note wierd the column aliases - thats how ActiveRecord knows what model to stuff each column in the result set into.

This will let you iterate through the associations without N 1 queries - like in this HTML example:

<article >
  <h1><%= @recipe.name %></h1>
  <ul >
  <%= @recipe.recipe_ingredients.each do |ri| %>
    <li>
      <%= ri.quantity %><%= ri.measurement_unit %> <%= ri.ingredient.name %>
    </li>
  <% end %>
  </ul>
</article>

Setting up an indirect assocation as in the other answers is generally a good idea but doing .eager_load(:ingredients) won't actually prevent a n 1 query when what you actually want to do is iterate across the recipe_ingredients assocation and the nested ingredient.

While you could render this as JSON with:

render json: @recipe, 
       include: { recipe_ingredients: :ingredient }

Its usually a better idea to move any kind of heavy lifting into a seperate serialization layer such as ActiveModel::Serializers or one of the many JSONAPI gems.