What is N+1 Query Problem? How do you solve it in Laravel?

Last Updated On - June 1st, 2024 Published On - May 18, 2024

Introduction

The N+1 query problem can be a common pitfall that impacts performance. In this tutorial, we’ll explore what the N+1 query problem is and how to solve it using Laravel’s Eloquent ORM.

Understanding the N+1 Query Issue

The N+1 query issue arises when we fetch related data in a loop without eager loading it. Suppose if we have N top-level categories, this results in N additional queries to fetch the subcategories for each category. Hence, the term “N+1” query, where N represents the initial query to fetch categories, and 1 represents each additional query to fetch subcategories.

Impact on Performance

Imagine if we have a large number of categories and subcategories. With the N+1 query issue, the number of database queries grows exponentially with the number of categories, leading to significant performance overhead. Each additional query adds latency to the response time, slowing down our application’s performance.

Also Read: Unlock The Ability of Google Maps API: 10x User Engagement

Database Setup

In this step, we focused on setting up our database structure using Laravel migrations. Migrations are a way to define and manage changes to our database schema over time. We’ll create a migration file to define the product_categories table to store information about different categories of products. Each category can have a parent category, forming a hierarchical structure. This is where the self-referential relationship comes into play.

What’s important to note here is the concept of a self-referential relationship. For example, a category like “Electronics” can have subcategories like “Mobile Phones” and “Laptops”, forming a parent-child relationship. We achieve this by adding a parent column to the table, which references the id column of the same table.

# Product categories model with migration
php artisan make:migration product_categories

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('product_categories', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100);
            $table->unsignedBigInteger('parent')->default(null)->nullable();
            $table->foreign('parent')->references('id')->on('product_categories')->onDelete('set null')->comment('Parent Category');
            $table->timestamps();
        });
    }

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

Routes Setup

In our routes file(web.php), we’ll define two routes: one to demonstrate the N+1 query issue and another to show its solution.

// routes/web.php

Route::get('/nplusoneissue', ['as' => 'nplusoneissue', 'uses' => 'ProductCategoriesController@index']);
Route::get('/nplusonesolution', ['as' => 'nplusonesolution', 'uses' => 'ProductCategoriesController@solution']);

Here, we define two routes in our routes/web.php file:

1. /nplusoneissue: This route will be used to demonstrate the N+1 query issue.
2. /nplusonesolution: This route will be used to demonstrate the solution to the N+1 query issue.

When a user visits /nplusoneissue, Laravel will invoke the index() method of our ProductCategoriesController, and when visiting /nplusonesolution, it will invoke the solution() method.

We’ll implement each associated method i.e. index() and solution() in the next step.

Also Read: Laravel 10 – Optimize and Upload Image to Azure Blob Storage

Controller Setup

Controllers are where the magic happens. They receive requests, process data, and return responses. In our ProductCategoriesController, we have two methods:

• index(): This method fetches top-level categories (categories with no parent) from the database and passes them to a view for rendering. However, it doesn’t eager load the subcategories, leading to the N+1 query issue.

• solution(): To solve the N+1 query issue, we use eager loading. This method fetches all categories along with their nested subcategories in a single query using the with() method.

<?php

namespace App\Http\Controllers;

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

class ProductCategoriesController extends Controller
{
    public function index()
    {
        $categories = Categories::whereNull('parent')->get();
        return view("backend.nplus1issue", compact("categories"));
    }

    public function solution()
    {
        $categories = Categories::with(['subCategories', 'subCategories.subCategories', 'subCategories.subCategories.subCategories'])
            ->whereNull('parent')
            ->get();
        return view("backend.nplus1solution", compact("categories"));
    }
}

Blade Templates – The View

Blade is Laravel’s powerful templating engine, allowing us to write HTML with embedded PHP code. In our case, we’ll use Blade to create two views to display our categories:

1. nplus1issue.blade.php: This view will render a table displaying categories without the solution to the N+1 query issue.
2. nplus1solution.blade.php: This view will render a table displaying categories with the solution to the N+1 query issue.

Both views will contain HTML markup for a table structure. Inside the table body, we’ll use Blade directives to loop through the categories and their subcategories and display them accordingly.

<!-- resources/views/backend/nplus1issue.blade.php -->
<!-- nplus1issue.blade.php -->


<div class="container">
    <h1 class="page-title">N+1 Query Issue</h1>
    <div class="row justify-content-center">
        <div class="col-md-12 table-container table-responsive">
            <table class="table">
                <thead>
                    <tr>
                        <th>Category Name</th>
                        <th>Level</th>
                        <th>Parent Category</th>
                    </tr>
                </thead>
                <tbody>
                    <!-- Display categories and their subcategories -->
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- resources/views/backend/nplus1solution.blade.php -->
<!-- nplus1solution.blade.php -->


<div class="container">
    <h1 class="page-title">N+1 Query Solution</h1>
    <div class="row justify-content-center">
        <div class="col-md-12 table-container table-responsive">
            <table class="table">
                <thead>
                    <tr>
                        <th>Category Name</th>
                        <th>Level</th>
                        <th>Parent Category</th>
                    </tr>
                </thead>
                <tbody>
                    <!-- Display categories and their subcategories -->
                </tbody>
            </table>
        </div>
    </div>
</div>

Also Read: Essential Laravel Packages: Must Install Before Starting Project

Demonstrating the Issue

When we visit the /nplusoneissue route in our application, the index() method is invoked. Inside this method, we fetch top-level categories and pass them to the view for rendering.

In the view (nplus1issue.blade.php), we iterate through the categories and display them in a table. However, for each category, we also attempt to display its subcategories. This is where the N+1 query issue becomes evident. As we loop through categories, Laravel executes additional queries to fetch subcategories for each category, resulting in poor performance.

Solution to N+1 Query Issue: Eager Loading

To solve the N+1 query issue and optimize our application’s performance, we’ll use eager loading. Eager loading allows us to fetch related data along with the primary data in a single query, rather than making separate queries for each related piece of data.

Implementing Eager Loading

In the solution() method of our ProductCategoriesController, we implement eager loading to fetch all categories along with their nested subcategories in a single query. Here’s how we achieve this:

public function solution()
{
    $categories = Categories::with(['subCategories', 'subCategories.subCategories', 'subCategories.subCategories.subCategories'])
        ->whereNull('parent')
        ->get();

    return view("backend.nplus1solution", compact("categories"));
}

In the with() method, we specify the relationships we want to eager load. By eager loading the subCategories relationship recursively, we fetch all subcategories at once, avoiding the N+1 query problem.

Impact of Eager Loading

With eager loading, we significantly reduce the number of database queries. Instead of executing N additional queries for each category’s subcategories, we fetch all related data in a single query. This results in improved performance and faster response times, especially when dealing with large datasets.

Also Read: VS Code Extensions For PHP, CodeIgniter 4, Laravel 11, WordPress

Demonstrating the Solution

When we visit the /nplusonesolution route in our application, the solution() method is invoked. Inside this method, we fetch all categories along with their nested subcategories using eager loading.

In the view (nplus1solution.blade.php), we iterate through the categories and display them in a table, similar to the previous demonstration. However, this time, there’s no N+1 query issue because we’ve eager loaded all related data upfront.

FAQs

Also Read: Laravel 10 Image Compression And Optimization Guide 2024

Conclusion

In this post, we’ve explored the N+1 query problem in Laravel and learned how to effectively address it using eager loading. By understanding and addressing the N+1 query issue using eager loading, developers can ensure optimal performance and scalability in their Laravel applications, providing a better experience for users and maintaining the efficiency of the application over time.

With this knowledge, you’re now equipped to identify and solve the N+1 query problem in your Laravel projects, ensuring efficient database operations and improved application performance.