Introduction

In this article, I'll describe the N+1 query issue and show you how to use eager loading to resolve it in a Laravel application.

What is an N + 1 Query?

When several queries are executed rather than a single optimal query to retrieve related records, the N+1 query problem arises. An inefficient method, for instance, would query all posts first, then run a different query for each post to retrieve its comments, when retrieving posts and their comments. Performance problems and slower response times emerge from this, as there are N queries for comments and one for postings.

The N+1 query problem occurs when the code first retrieves a collection of records with one query and then runs an additional query for each record to fetch related data. If N represents the number of records in the initial collection, this results in a total of N+1 queries, leading to unnecessary database load and performance issues.

The Problem with N + 1 query

At first glance, the N+1 query problem may not seem like a concern for small applications with limited data. However, imagine fetching posts from a database containing over a million posts and millions of comments. If each post triggers a separate query to retrieve its comments, the number of queries grows rapidly, causing significant delays, performance issues, timeouts, and high resource consumption.

This unnecessary increase in database queries severely impacts performance, especially when dealing with large datasets.

For example:

Let’s say you are displaying a list of 100 posts with their comments:

        $posts = Post::all();

        foreach ($posts as $post) {
            echo "Post: " . $post->title . "\n"."<br/>";
            foreach ($post->comments as $comment) {
                echo "- Comment: " . $comment->body . "\n"."<br/>";
            }
        }

• Eloquent will make one query for those 100 posts;

• Then, 100 queries for each comments, because the user relationship is lazily loaded (meaning it’s loaded each time you call $post->comments in your code).

Total Queries:
1 (posts) + 100 (comments for each post) = 101 queries.

By default Laravel uses lazy loading to handle the retrieval of related model records. First retrieve the posts and then later access the related comments:

You could see there are 101 queries been executed.

        DB::enableQueryLog();
        dd(DB::getQueryLog());

I used QueryLog to view the number of queries performed but you could use Laravel Telescope or Laravel Debuggar for better display of the queries.

I’ve a video on my Youtube Channel where i explained how to use Laravel Telescope:

Summary of Queries:

• 1 Query to fetch all posts from the posts table.

SELECT * FROM `posts`;

• N Queries to fetch comments for each post, where N is the number of posts retrieved in the first query.

SELECT * FROM `comments` WHERE `comments`.`post_id` = 1 AND `comments`.`post_id` IS NOT NULL;
SELECT * FROM `comments` WHERE `comments`.`post_id` = 2 AND `comments`.`post_id` IS NOT NULL;
..........
..........
..........
SELECT * FROM `comments` WHERE `comments`.`post_id` = N AND `comments`.`post_id` IS NOT NULL;

Now you may see there are N+1 queries. This is called N+1 issue.

How to Handle N + 1 Query issue

The best way to avoid the N+1 query problem is to explicitly define which related records should be retrieved in a single query, preventing unnecessary multiple queries. In this article, I’ll show you how to handle N+1 queries efficiently in a Laravel application.

Eager Loading

Eager loading in Laravel is a technique that retrieves related records in a single query, preventing the N+1 query problem and improving performance.

• with()

        DB::enableQueryLog();

        $posts = Post::with('comments')->get();

        foreach ($posts as $post) {
            echo "Post: " . $post->title . "\n"."<br/>";
            foreach ($post->comments as $comment) {
                echo "- Comment: " . $comment->body . "\n"."<br/>";
            }
        }

        dd(DB::getQueryLog());

//this will retrieve posts with their corresponding comments with a single query

• Retrieve all posts:

SELECT * FROM posts;

• Retrieve all comments for the retrieved posts

SELECT * FROM comments WHERE post_id IN (1, 2, 3, 4, 5...);

Total Queries:
1 (posts) + 1 (all comments) = 2 queries only.

Outcome:

• without()

Simply add the without method to the query if we ever need to remove the global relationship from $with for a single query.

$posts = Post::without('comments')->get();

• withOnly()

Additionally, we can use the withOnly function in this way to override every item in the $with for a single query.

$posts = Post::withOnly('comments')->get();

Alternative Approach

• select

If you don’t want the comments to be pulled alongside the posts you can use the select statement:

        $posts = Post::select('title', 'content')->get();

        foreach ($posts as $post) {
            echo "Post: " . $post->title . "\n"."<br/>";
            foreach ($post->comments as $comment) {
                echo "- Comment: " . $comment->body . "\n"."<br/>";
            }
        }
//since the post id isnt selected the related records won't be retrieved

whereHas()

The whereHas() is method use to specify that retrieve posts that has comments compared to the case of using with() method alone it gets to retrieve all related comments at once even if the post does not have a comment. whereHas acts like a filter at database level.

        $posts = Post::whereHas('comments')->with('comments')->get();

        foreach ($posts as $post) {
            echo "Post: " . $post->title . "\n"."<br/>";
            foreach ($post->comments as $comment) {
                echo "- Comment: " . $comment->body . "\n"."<br/>";
            }
        }

• where()

Furthermore, you can filter the related comments by applying a condition within the where() method inside the callback function. For example, to retrieve only comments with a status of true, you can refine the query as follows:

$posts = Post::whereHas('comments', function ($query) {
    $query->where('status', true);
})->with('comments')->get();

Just as shown above using select(), you can also specify the columns to select for better performance and speedy response:

$posts = Post::select('ulid', 'id', 'titile', 'description', 'created_at')->whereHas('comments')->with('comments:id,body,status')->get();

Or you could use addSelect() method:

$users = Post::with('comments')
    ->select('ulid', 'id', 'titile', 'description', 'created_at')
    ->addSelect('comments.id', 'comments.description', 'comments.status')
    ->get();

• Pluck()

When retrieving a single column from a table, you can use the pluck() method, which executes a single query and helps prevent the N+1 query problem.

$postUlids = Post::pluck('title');
//say probably you need only title from the posts tables

• count()

If you need to display the total number of posts on the website, avoid chaining count() with all(), as it loads all records into memory before counting. Instead, use count() directly on the query for better performance.

$posts = Post::all()->count(); //loads all record into memory before counting

$posts = Post::count();

That’s resource intense for to retrieve the number of posts. But you can use count() to achieve that:

• count() each comments on a loop (not recommended)

Counting comments for each post inside a loop is resource-intensive. This can be optimized using withCount(), which retrieves the count in a single query.

Instead of this: 
$posts = Post::get();

foreach ($posts as $post) {
    $comments = $post->comments;
    echo count($comments);
}

withCount()

The withCount() method can be used to retrieve the number of comments associated with a particular post instead pulling the whole record and counting each item:

$posts = Post::withCount('comments')->get();

foreach ($posts as $post) {
    echo $post->comments_count;
}

• PreventLazyLoading()

To monitor and fix lazy loading issues, you can enable preventLazyLoading() in the AppServiceProvider. It's best to enable this only in the local environment to avoid breaking code in production.

namespace App\Providers;

use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        Model::preventLazyLoading(! app()->isProduction());
    }
}

When you try the code below locally it’ll throw an error.

$posts = Post::get();
foreach ($posts as $post) {
    $comments = $post->comments;
    echo $comments;
}

That being said, let’s wrap it up. In my upcoming articles, I’ll explore various Eloquent methods and their best use cases to help you write more efficient queries.

Conclusion

In this article, we explored the N+1 query problem, how to handle it effectively, and methods for optimizing database queries when retrieving related records. Laravel’s eager loading (with()) is a powerful solution to the N+1 issue, reducing the number of queries and improving application performance. For large datasets with multiple related records, using join() can be a more efficient alternative. To monitor and optimize query performance, QueryLog() method, tools like Laravel Telescope or Laravel Debugbar can help identify and resolve inefficiencies, ensuring a faster and more scalable application for both web and API development.

Trust you find this article useful… kindly share with your network and feel free to use the comment section for questions, answers, and contributions.