phplaraveleloquentpivot

Laravel N+1 when accessing a pivot relationship and the related model (unit)


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

Solution

  • 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');
    }