Laravel, N+1 query and eager loading

The N+1 query is from one of the most noted performance issues that can occur when working with databases in Laravel (or any kind of programming framework). It appears when a single database query is used to fetch a parent record and then additional query are made for each related child record.

For example, consider a shop application with a categories table and a products table. If you wanted to display a list of all the categories in the shop, along with the number of products for each category, you might write a query like this:

$categories = Category::all();

foreach ($categories as $category) {
    $countProducts = Product::where('category_id', $category->id)->count();
    echo "Category: " . $category->name . " - Products: " . $countProducts;
}

This would work fine for a small number of categories, but as the number of categories grows, the performance of this code will decrease because it is making a separate database query for each category to count the number of products. This is an example of an N+1 query.

Eager loading

To fix this issue, you can use a technique called “eager loading” to load the related products for all the categories in a single database query. In Laravel, you can use the with method to eager load the products for each category:

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

foreach ($categories as $category) {
    $countProducts= count($category->products);
    echo "Category: " . $category->name . " - Products: " . $countProducts;
}

This will execute a single query to fetch all the categories and their related products through the relation used, rather than making a separate query for each category. This can significantly improve the performance of your application when working with large amounts of data.

In conclusion, is important to note that N+1 query can appear with any type of a database relationship (for example: one-to-one, one-to-many, many-to-many). Eager loading can be used to optimize any of these relationships to prevent N+1 query issue.

If you like my article hit the follow button for more good articles like this one. :)