I got n+1 query while working with my models and pivot
Recipe.php:
public function user(): BelongsTo
{
return $this->belongsTo(User::class);
}
public function ingredients(): BelongsToMany
{
return $this->belongsToMany(Ingredient::class)
->using(IngredientRecipe::class)
->withTimestamps()
->withPivot(['quantity', 'unit_id']);
}
IngredientRecipe.php (Pivot):
public function unit(): BelongsTo
{
return $this->belongsTo(Unit::class);
}
Ingredient.php:
public function recipes(): BelongsToMany
{
return $this->belongsToMany(Recipe::class);
}
Unit.php:
public function ingredient_recipes(): HasMany
{
return $this->hasMany(IngredientRecipe::class);
}
What am I trying to do:
In my user-profile page I want to show a list of recipes for owner (user). Every recipe has ingredients()
which are in pivot table with additionall columns quantity
and unit_id
Code:
In my ProfileController.php
I am using this code:
public function show_profile(User $user)
{
$userRecipes = $user->recipes()->with('ingredients', 'guideSteps')->get();
return view('user.user-profile', compact('user', 'userRecipes'));
}
Problem:
Laravel doesn't know that pivot->unit_id
is related to the Unit
model, so every I am accessing pivot->unit
, it makes a separate query to the units
table.
In debugbar I am getting 15 duplicated queries:
select * from users
where users
.id
= 1 limit 1
select * from units
where units
.id
= 3 limit 1
select * from units
where units
.id
= 3 limit 1
... 12 more
And a problem is in this place:
@foreach($userRecipes as $recipe)
<x-recipe-card :recipe="$recipe"/>
@endforeach
---------------inside component recipe-card:------------------------
@foreach($recipe->ingredients as $ingredient)
<div>
<span>{{ $ingredient->name }}</span>
<div></div>
<span>{{ $ingredient->pivot->quantity . ' '. $ingredient->pivot->unit->name}}</span>
</div>
@endforeach
Laravel will not automatically eager-load relationships defined inside pivot models.
Try something like this (Not tested)
composer require ajcastro/eager-load-pivot-relation
public function show_profile(User $user)
{
$userRecipes = $user->recipes()
->with(['ingredients.pivot.unit', 'guideSteps'])
->get();
return view('user.user-profile', compact('user', 'userRecipes'));
}
EDIT 02
public function ingredients(): BelongsToMany
{
return $this->belongsToMany(Ingredient::class)
->using(IngredientRecipe::class)
->withTimestamps()
->withPivot(['quantity', 'unit_id'])
->with('pivot.unit');
}