Laravel Eloquent N+1 Problem and Solving (detailed performance optimization)

4 min readJul 6, 2022

Hello everyone, hope so everything is fine. Today, I want to talk about the N+1 query problem in Laravel.

Sometimes, we need to show our relational items in a loop, which creates other problems like performance and unnecessary queries in our collection. Firstly I’m gonna show that problem then we’ll find a solution to that question of how can we prevent this problem.

So, there are users and their posts and wallet histories in our database.

Let's create our models…

// Models/User.php
<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Relations\HasMany;
/**
 * App\Models\User.
 *
 * @property int $id
 * @property string $first_name
 * @property string $last_name
 * @property string $email
 * @property Carbon $created_at
 * @property Carbon $updated_at 
*/
class User extends Authenticatable
{
    public function posts(): HasMany
    {
       return $this->hasMany(Post::class, 'user_id', 'id');
    }
    public function walletHistory(): HasMany
    {
       return $this->hasMany(WalletHistory::class, 'user_id', 'id');
    }
}

wallet history model;

// Models/WalletHistory.php
<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
/**
 * App\Models\WalletHistory
 *
 * @property int $id
 * @property int $user_id
 * @property float $value
 * @property Carbon $created_at
 * @property Carbon $updated_at 
*/
class WalletHistory extends Model
{
    public function user(): BelongsTo
    {
       return $this->belongsTo(User::class, 'user_id', 'id');
    }
}

and post model should kinda that;

// Models/Post.php
<?phpnamespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;/**
 * App\Models\Post
 *
 * @property int $id
 * @property int $user_id
 * @property string $title
 * @property string $content
 * @property Carbon $created_at
 * @property Carbon $updated_at 
*/
class Post extends Model
{
    public function user(): BelongsTo
    {
       return $this->belongsTo(User::class, 'user_id', 'id');
    }
}

And we need a resource for beauty response;

// Http/Resources/User/UserResource.php
<?php

namespace App\Http\Resources\User;

use Illuminate\Contracts\Support\Arrayable;
use Illuminate\Http\Resources\Json\JsonResource;
use JsonSerializable;

class UserResource extends JsonResource
{
    
    public function toArray($request):
array|JsonSerializable|Arrayable
    {
        return [
            'id'               => $this->id,
            'name'             => $this->name,
            'posts'            => $this->posts,
            'posts_count'      => $this->posts()->count(),
            'wallet_histories' => $this->walletHistories,
            'wallet_transaction_count' => $this->walletHistories()->count(),
            'wallet_balance'   => $this->walletHistories()->sum('value'),
            'created_at'       => $this->created_at,
            'updated_at'       => $this->updated_at,
        ];
    }
}

So, we can create our controller for the response…

// Http/Controller/UserController.php
<?php

namespace App\Http\Controllers\User;

use App\Http\Controllers\Controller;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use App\Http\Resources\User\UserResource;

class UserController extends Controller
{
    public function __invoke()
    {
        $query = User::limit(50)->orderBy('id', 'DESC')->get();
        return UserResource::collection($query);
    }
}

It seems everything is fine, how can we show query count in this position. Normally, we use the debugger package, but our data is not showing in the HTML file, so we can not pass our query logs into the result page.

We can use Telescope in that position. It’s also a useful package that is supported by Laravel.

composer require laravel/telescope
php artisan telescope:install
php artisan migrate
php artisan serve

We can check our request via that URL; http://127.0.0.1:8000/telescope

And results.. 🤦‍♂️

Laravel Telescope is also detected most of is duplicated. That’s incredible, right?

We must change something in our resource and query… Firstly I’d like to say, if we use brackets while we use our relationship, it’s also ready for a new Query.
I mean, we used most of the time $this->posts()->count()
We can change that lines as like $this->posts->count()
It means, Laravel won’t create a new query, the model also has posts. So, Laravel will have found relations count via the Collection package, not with query. Let’s try…

<?php

namespace App\Http\Resources\User;

use Illuminate\Contracts\Support\Arrayable;
use Illuminate\Http\Resources\Json\JsonResource;
use JsonSerializable;

class UserResource extends JsonResource
{
    
    public function toArray($request): array|JsonSerializable|Arrayable
    {
        return [
            'id'               => $this->id,
            'name'             => $this->name,
            'posts'            => $this->posts,
            'posts_count'      => $this->posts->count(),
            'wallet_histories' => $this->walletHistories,
            'wallet_transaction_count' => $this->walletHistories->count(),
            'wallet_balance'   => $this->walletHistories->sum('value'),
            'created_at'       => $this->created_at,
            'updated_at'       => $this->updated_at,
        ];
    }
}

and results…

So, we understood brackets created many queries because it’s not a prepared class property. We call the Model function, so it creates new queries via Eloquent.

But we still have problems, it’s not enough…
So should add our relations with (with) command into the query.

<?php
namespace App\Http\Controllers;

use App\Http\Resources\User\UserResource;
use App\Models\User;

class UserController extends Controller
{
    public function __invoke()
    {
        $query = User::with(['posts', 'walletHistories'])
                ->limit(50)
                ->orderBy('id', 'DESC')
                ->get();
        
        return UserResource::collection($query);
    }
}

No longer, we finally decrease our query count. It’s enough but we can also add Aggregate Functions in our query.

What does that mean?
We calculate our wallet balance, posts, and transaction count via PHP function with Laravel Collection, I mean, I can directly get from the DB.

withCount method receive an array, and these are our Model relations. So, It gives {converted_snake_case_method_name}_count property.

For example, we have walletHistories method.
We use $model::withCount([‘walletHistories’]))->get();
It gives wallet_histories_count property for us.

Look at the result;

You can use those tips in your project if it has already kinda these useless things…

Have a nice day and happy code.

Github Repository:
https://github.com/muhammetakkurt/L9-Eloquent-N1-Example

Laravel Eloquent N+1 Problem and Solving (detailed performance optimization)

Written by Muhammet AKKURT