Share

In this article we will describe the Eloquent method in Laravel 11 chaperone and the related N+1 query problem in database applications. We will describe and resolve the problem using native PHP and then look at how laravel 11 solves this using eloquent chaperone method.

N+1 Query Problem

N+1 Query problem refers to a problem happens in database apps when you execute N additional queries to retrieve the same set of data that can be retrieved using a single query. Typically this issue happens in one to many relationships when retrieving a parent table data and then perform another query to get the related data from another table.

An example of this when you have two tables as shown:

• categories
• posts

The structure of these tables described in this Erd diagram

As you see the posts and categories table have a relation one to many so the categories have many posts.

Using native php we need to retrieve the categories, and then inside each category we need to retrieve the related posts:

<?php

$conn;

try {
  // DB connection logic is hidden to focus on actual query
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}

$start_time = microtime(TRUE);

$cats_query = $conn->prepare("SELECT * FROM categories");
$cats_query->execute();

while ($row = $cats_query->fetch()) {
   $arr = [
        'c_id' => $row['id'],
        'c_title' => $row['title'] 
    ];

   $posts_query = $conn->prepare("SELECT * FROM posts WHERE category_id = :category_id");
    $posts_query->bindParam(':category_id', $row['id']);
    $posts_query->execute();

    while ($row_post = $posts_query->fetch()) {
    	$arr['c_posts'][] = [
    		'title' => $row_post['title'],
                'content' => $row_post['content']
    	];
    }

    $data[] = $arr;
}

$end_time = microtime(TRUE);

// Print the elapsed time
print($end_time - $start_time);

In this code there are two queries to fill the $data array. One that fetch the list of categories. Next we iterate over each category using a while loop. Inside the main loop another query added to fetch the posts using category_id foreign key. fill in the $data array.

Here is the problem in the second query. This query will be executed multiple times depending on the number of categories in the categories table meaning the code will make several round trips to the database server which affects the performance. So if the categories table have 10 categories then it will execute 10 times in addition to the main query so generally speaking 11 round trips, and in each time it will fetch the related posts by category.

For this comes the term N+1 Query because we are doing 1 query (in this case the main query that fetch the categories) and +1 (the additional queries that fetch the posts).

Whenever you stuck in a similar case like this one, you are likely have the N+1 Query issue. 

I have tested this code with 5000 records in the posts table and 10 category items in the categories table, then i checked the elapsed time it give me about:

0.01430 seconds

And this is of course a big time for this query. Imagine you have millions of records.

What is the solution for N+1 Query Problem

As mentioned previously the N+1 Query problem arises when making two queries to fetch the same data which can be rewritten in single query. To resolve this, instead on doing the db logic in two queries, we have to perform this in a single query using Joins or subqueries. 

$start_time = microtime(TRUE);

$query = $conn->prepare("SELECT c.id as cat_id, c.title as cat_title, p.title as post_title, p.content as post_content FROM categories c
        LEFT JOIN posts p ON c.id = p.category_id
    ");

$query->execute();

$data = [];
$posts = [];

while ($row = $query->fetch()) {

    $data[$row['cat_id']] = [
        'c_id' => $row['cat_id'],
        'c_title' => $row['cat_title']
    ];

    $posts[$row['cat_id']][] = [
            'title' => $row['post_title'],
            'content' => $row['post_content']
    ];

    $data[$row['cat_id']]['c_posts'] = $posts[$row['cat_id']];
}

$end_time = microtime(TRUE);


print($end_time - $start_time);

As shown in the code i wrote only one query to fetch the categories and posts using join statement between categories and posts.

When checking the elapsed time the performance significantly improved:

0.00152 seconds

Laravel and N+1 Query

In laravel framework if you are writing your queries using Eloquent ORM you typically encountered the N+1 Query problem under the hood when making one to many relationships using some code like this:

$categories = Category::with('posts')->get();

foreach ($categories as $category) {
    foreach ($category->posts as $post) {
        echo $post->title;
    }
}

app/Models/Category.php

class Category extends Model
{
    use HasFactory;

    public function posts() : HasMany
    {
        return $this->hasMany(Post::class, 'category_id');
    }
}

This code have the same N+1 effect as in the first example above.

When viewing the queries on Laravel telescope, there are about 9 queries:

In Laravel 11 the laravel team added a new feature which is the Eloquent method chaperone(). This method resolve the N+1 Query: 

class Category extends Model
{
    use HasFactory;

    public function posts() : HasMany
    {
        return $this->hasMany(Post::class, 'category_id')->chaperone();
    }
}

When applying this method to the relation and checking Laravel telescope again:

As shown there is only one two queries executed.