2024 UPDATE - LARAVEL 11

We're excited to announce that we have updated all of our blog post examples to reflect the new Laravel 11 version! Our previous examples were based on Laravel 10, but with the release of Laravel 11, we wanted to ensure that our readers have access to the most up-to-date information and examples.

Tackling the N+1 Problem in Laravel

When developing an application in Laravel, you might face a common performance issue known as the N+1 query problem. It occurs when your code executes an additional query for every result of an initial query, leading to a significant decrease in performance and quality.

What is the N+1 Query Problem?

Imagine you have a blog where each post can have many comments. You want to display all posts along with their comments. A naive approach might look like this:

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
 public function index()
	{
		$posts = Post::all();

		return view('posts.index', ['posts' => $posts]);
	}
}

And we would show all comments in our blade file like this:

<div>
	@foreach ($posts as $post)
		<h1>{{ $post->title }}</h1>
  
		@foreach ($post->comments as $comment)
			  <p>{{ $comment->content }}</p>
		@endforeach
	@endforeach
</div>

In this scenario, Laravel performs one query to retrieve all posts and then an additional query for the comments of each post. If you have N posts, this results in N+1 queries to the database: one for the posts and N for the comments. This is the N+1 problem.

Lazy Loading and the N+1 Problem

Lazy loading is the default behavior of Eloquent relationships, meaning related models are not loaded until you access them. While this can be convenient, it can also lead to the N+1 problem if not managed carefully.

Eager Loading to Solve the N+1 Problem

The solution to the N+1 problem is eager loading, which allows you to load all related models in a single query. You can achieve this using the with method. Look at the example below.

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
 public function index()
	{
		$posts = Post::with('comments')->get();

		return view('posts.index', ['posts' => $posts]);
	}
}

Our Blade file stays the same, no changes are needed there.

Now, Laravel executes just two queries: one to retrieve all posts and one to retrieve all comments related to those posts.

Detecting Query Problems with Laravel Debugbar

Laravel Debugbar is an open-source package that adds a developer toolbar to your application for debugging. It can display various metrics, including queries, which makes it an invaluable tool for identifying the N+1 query problem and other query-related issues.

Installing Laravel Debugbar

To install Laravel Debugbar, you need to require it via Composer:

composer require barryvdh/laravel-debugbar --dev

Laravel Debugbar will automatically register itself through Laravel's package discovery. Once installed, it will appear at the bottom of your application's pages when in the local environment.

Detecting Query Issues

With Laravel Debugbar enabled, navigate through the pages of your blog where you list posts, view single posts, or display comments. Keep an eye on the "Queries" tab in the Debugbar. If you see a high number of queries, especially a pattern of repeated queries that increase with the number of posts or comments displayed, you may have an N+1 problem.

Using Debugbar to Optimize Queries

Laravel Debugbar isn't just for detecting the N+1 problem. It's also useful for general query optimization. For instance, you can:

Identify slow queries that need optimization.
Check if indexes are being used properly.
Monitor the number of queries triggered by a single request.
Confirm that eager loading is applied where it should be.

Testing the N+1 problem

We don't have the comments feature on our blog, so we can't test the code above. Let's do this now!

If you remember, we start by creating the model and using the flags to create the controller, migration, seeder and factory:

php artisan make:model Comment -mfsc

Now we need to modify our Post model and include the relationship with the comments:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
	public function user()
    {
        return $this->belongsTo(User::class);
    }
	public function tags()
    {
        return $this->belongsToMany(Tag::class);
    }
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

We need to do the same on the User model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Authenticatable
{
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

Now let's do the inverse relationship on the Comment model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

Then we need to modify the Comment migration file so we can make sure the comment is connected with the user who writes it and the post where it's written:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCommentsTable extends Migration
{
    public function up()
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('post_id')->constrained()->onDelete('cascade');
            $table->foreignId('user_id')->constrained()->onDelete('cascade');
            $table->text('body');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('comments');
    }
}

Next up, let's modify the Factory of the Comment:

<?php

namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;

class CommentFactory extends Factory
{
    public function definition()
    {
        return [
			'user_id' => User::inRandomOrder()->first()->id,
			'post_id' => Post::inRandomOrder()->first()->id,
            'body' => fake()->paragraph,
        ];
    }
}

Now we modify the Seeder:

<?php

use Illuminate\Database\Seeder;
use App\Models\Image;
use App\Models\Post;

class CommentSeeder extends Seeder
{
    public function run()
    {
		Comment::factory()->count(200)->create();
    }
}

Let's add this Seeder to the Database Seeder group:

<?php

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run()
    {
        $this->call([
            UserSeeder::class,
            PostSeeder::class,
            TagSeeder::class,
            ImageSeeder::class,
            CommentSeeder::class,
        ]);
    }
}

Let's run the migration command to include the newly added comments table. However, since we're not live yet, we can run the following command that will erase all previous data and populate the tables with random data:

php artisan migrate:fresh --seed

NOTE: This will remove all previous data and start fresh.

Using Laravel Debugbar to identify N+1 query issue

With Laravel Debugbar installed, you can see how the Query number will change when you try to use eager loading on your PostController index page.

Without Eager Loading (N+1 Problem appears):

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
 public function index()
	{
		$posts = Post::all();

		return view('posts.index', ['posts' => $posts]);
	}
}

The Laravel Debugbar output indicates that there are 1003 queries. We perform a single query to retrieve all 1000 posts. However, we must query the database again for each post to obtain its comments, resulting in an additional 1000 queries for the comments of the posts.

With Eager Loading:

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
 public function index()
	{
		$posts = Post::with('comments')->get();

		return view('posts.index', ['posts' => $posts]);
	}
}

The Laravel Debugbar output indicates that there are 4 queries. We use one query to retrieve all 1000 posts. Then, for all the retrieved posts, we query the database just once, resulting in only one extra query. We do this before executing the foreach statement on our frontend.

Best Practices for Eloquent ORM

To ensure your Laravel blog remains performant and free of the N+1 problem, follow these best practices:

Eager Load Relationships: Whenever you're displaying related models, use eager loading to prevent the N+1 problem.
Use the lazy Method for Large Datasets: If you're dealing with a large number of models and related data, consider using the lazy method to chunk the results and reduce memory usage.
Monitor Queries with Laravel Debugbar: Tools like Laravel Debugbar can help you monitor the queries executed by your application, making it easier to spot N+1 problems.
Take Advantage of Relationship Counting: When you need to display the count of related models, use the withCount method to optimize the query $posts = Post::withCount('comments')->get();
Limit the Number of Eager Loaded Models: Be cautious not to over-eager-load. Only load the relationships you need for the current operation.
Use Lazy Eager Loading When Necessary: If you didn't eager load initially and need to work with related models later, use the load method to perform lazy eager loading $post->load('comments');
Consider Using Caching: For data that doesn't change frequently, such as a list of categories or tags, use caching to store and retrieve the data instead of hitting the database each time.

Best Practices for Using Laravel Debugbar

While Debugbar is a powerful tool, here are some best practices to keep in mind:

Use in Development Only: Debugbar should only be used in your local development environment. Make sure it's not included in your production deployments.
Analyze Query Details: Use the detailed query information provided by Debugbar to understand how your queries are constructed and how they can be optimized.
Profile Regularly: Regularly profile your application with Debugbar as you develop new features to catch potential performance issues early.
Keep Debugbar Updated: Ensure you have the latest version of Debugbar to take advantage of any performance improvements and new features.

Conclusion

The N+1 query problem can significantly impact the performance of your Laravel blog if not addressed properly. Laravel Debugbar is an essential tool for any developer looking to optimize their blog's performance. By providing real-time insights into your application's queries, Debugbar helps you detect and solve issues like the N+1 query problem, ensuring your blog remains fast and responsive.

Upcoming Articles in the Series

Laravel 11 for Beginners: API Resources
Laravel 11 for Beginners: API Routes with Laravel Sanctum
Laravel 11 for Beginners: CSRF Protection explained

This article is part of our series Laravel 11 for Beginners: A Step-by-Step Guide to Learn the Concepts.

Bring Your Ideas to Life 🚀

If you need help with a Laravel project let's get in touch.

Lucky Media is proud to be recognized as a Top Laravel Development Agency

Laravel 11 for Beginners: Query Performance Issues and Debugbar