如果我有一個連接兩個表的連接模型，如何在一個查詢中獲取所有需要的記錄？

我有三個表食譜、成分和食譜成分（加入前兩個表）

在我的 recipe_controller 中，我想讓我的 index 方法回傳所有需要的資料以向用戶顯示食譜。

架構看起來像（洗掉了無用的資訊）：

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

我試過類似的東西

  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

至少讓一條記錄正確，但我不確定在嘗試創建通用索引方法而不撰寫討厭的、低效的回圈時從哪里開始。

另外，我是 Rails sql 的新手，這很棒，但我確定我在這里錯過了很多優化，所以如果我可以做些什么來改進上述內容，請告訴我。

uj5u.com熱心網友回復：

我認為這里的問題在于你的成分。

首先你可以像這樣得到recipe_ingredients

recipe_ingredients = recipe.recipe_ingredients

另一件事是你沒有得到成分的ID，但這些是recipe_ingredients，它是橋表。

ingredient_ids = recipe_ingredients.pluck(:ingredient_id)

現在你的代碼應該像

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 }

結尾

uj5u.com熱心網友回復：

您可以在您的接收者模型中創建以下關聯

has_many :recipe_ingredients
has_many :ingredients, through: :recipe_ingredients

在你的控制器中

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

這將在單個查詢中加載所有recipies、recipe_ingredients 和成分。在您看來，您可以迭代@recipies

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

更新：

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

uj5u.com熱心網友回復：

我假設您實際上并不想要那種 JSON 結構。僅僅因為考慮到總體目標，它實際上沒有意義。如果您想渲染一個配方，您希望實際將數量、名稱和單位放在一起，這樣您就不會在消費端從兩個單獨的陣列中拼湊所需的資訊。

理想情況下，您需要類似于以下內容的 JSON：

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

前提是您有以下關聯：

class Recipe < ApplicationRecord
  has_many :recipe_ingredients
end

class RecipeIngredient < ApplicationRecord
  belongs_to :recipe
  belongs_to :ingredient
end

您可以在單個查詢中從所有三個表中加載記錄：

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

這會生成以下怪物查詢：

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

注意奇怪的列別名 - 這就是 ActiveRecord 如何知道將結果集中的每一列填充到哪個模型中。

這將允許您在沒有 N 1 查詢的情況下遍歷關聯 - 就像在這個 HTML 示例中一樣：

<article class="recipe">
  <h1><%= @recipe.name %></h1>
  <ul class="ingredients">
  <%= @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.

標籤：轨道上的红宝石 PostgreSQL

上一篇：如何將天轉換為分鐘

下一篇：無限制的解決方案