Laravel – Preventing n+1 queries problem

The n+1 query problem occurs in various systems based on ORM (Object-Relational Mapping), and Laravel is no exception. The n+1 query problem manifests when an application sends too many queries to the database. The larger the dataset we retrieve in this way, the more we can overload our database with excessive queries.

Lazy loading vs. Eager Loading

Lazy loading is useful when we want to fetch a small amount of data. Related tables are loaded only when it’s necessary. But the larger the database becomes, the larger the problem becomes. Operating on data of which size we don’t know might become a problem. Preloading the data on which we operate might become a preferable solution.

$shop = Shop::find(1)->get();
foreach ($shop->products as $product) {
   //Do something ...
}

In such an example, products will be loaded via lazy loading. Each individual record of $product related to $shop through a relationship will be loaded as a separate query.

Eager loading does not have an issue with n+1 queries. We fetch the full set of data at once, which may affect the size of the packets we send and prolong the query processing time. However, we certainly won’t burden the database with excessive queries.

$shop = Shop::find(1)->with(['products'])->get();
foreach ($shop->products as $product) {
   //Do something ...
}

In such a case, the data from the Products table will be fetched along with the Shop with the specified ID. The database will receive only two queries: the first for the record from the Shops table and the second for the records from the Products table related by a foreign key to the Shops table.

Problem detection

Searching for queries with the n+1 queries problem can be done by analyzing the application logs. But first, we need to add code that will start logging all database queries sent to the application logs. Then, through log analysis, we can identify if any of the queries are executed n+1 times.

To achieve this, simply add the following code.

// /app/Providers/AppServiceProvider.php
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;


// ...


/**
* Bootstrap any application services.
*/
public function boot(): void
{
   DB::listen(function (QueryExecuted $query) {
       Log::debug($query->sql);
   });
}

Protecting ApiResources from lazy loading

Lazy loading can also occur when returning an API resource and referencing other models and resources within it. If we do not add a condition to check if the attribute has been loaded, the API resource will execute additional database queries to load the missing values. Laravel provides a set of conditions that we can use within the API resource to block lazy loading.

The full list of functions can be found in the Laravel documentation.

/**
* Transform the resource into an array.
*
* @return array<string, mixed>
*/
public function toArray(Request $request): array
{
   return [
       'when' => $this->when(), 
// Adds an attribute only when the condition is met
       'whenLoaded' => $this->whenLoaded(), 
// Adds an attribute when the relationship has been loaded
       'whenAggregated' => $this->whenAggregated(), 
// Adds an attribute when we specify how and on which data the aggregation was performed
   ];
}

Disabling lazy loading

Laravel also allows for the complete disabling of lazy loading in the application. By adding the following code, we can ensure that every instance of lazy loading will be blocked by the application, informing us of which model and relationship caused the issue and ultimately resulting in an exception. However, if you prefer not to block lazy loading in all models, you can limit it by calling limitLazyLoading() on a specific model.

// /app/Providers/AppServiceProvider.php
<?php


// ...


class AppServiceProvider extends ServiceProvider
{
   /**
    * Bootstrap any application services.
    */
   public function boot(): void
   {
       //Prevent lazy loading on all models
       Model::preventLazyLoading();
       //Prevent lazy loading on User model
       User::preventLazyLoading();
   }
}