The N+1 Problem is one that exists in most ORMs, or Object Relational Mapper tools. The N+1 problem happens as a result of so called “lazy-loading”. Lazy loading of data from the database happens when a query is made for a parent record, and then an additional query for every single child record. In this tutorial, we’ll take a look at how to solve the N+1 problem by making use of a technique called Eager Loading. We’ll also inspect how to debug these excessive queries using a dedicated tool.
Debugging SQL Queries With Laravel Debug Bar
To start debugging some sql queries, let’s grab the https://github.com/barryvdh/laravel-debugbar and install it now. At the terminal:
vagrant@homestead:~/Code/forumio$ composer require barryvdh/laravel-debugbar --dev Using version ^3.1 for barryvdh/laravel-debugbar ./composer.json has been updated Loading composer repositories with package information Updating dependencies (including require-dev) Package operations: 2 installs, 0 updates, 0 removals - Installing maximebf/debugbar (v1.14.1): Downloading (100%) - Installing barryvdh/laravel-debugbar (v3.1.0): Downloading (100%) maximebf/debugbar suggests installing kriswallsmith/assetic (The best way to manage assets) maximebf/debugbar suggests installing predis/predis (Redis storage) Writing lock file Generating optimized autoload files > IlluminateFoundationComposerScripts::postAutoloadDump > @php artisan package:discover Discovered Package: fideloper/proxy Discovered Package: laravel/tinker Discovered Package: barryvdh/laravel-debugbar Package manifest generated successfully.
Register With AppServiceProvider
A nice way to conditionally load the debug bar is to set it up in the AppServiceProvider as shown below. If you don’t want the debug bar running in a production environment, and of course you wouldn’t, this will load the application service only in a local environment.
<?php
namespace AppProviders;
use AppChannel;
use function foofunc;
use IlluminateSupportServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
View::composer('*', function ($view) {
$view->with('channels', Channel::all());
});
}
/**
* Register any application services.
*
* @return void
*/
public function register()
{
if ($this->app->isLocal()) {
$this->app->register(BarryvdhDebugbarServiceProvider::class);
}
}
}Reloading the application shows us that the debug bar is now in fact running. Notice we can click on various options to see really nice information about the running application. In our case, we want to focus on the high number of SQL queries happening. Clicking the Queries tab shows a whopping 57 queries! Lazy loading is to blame here, and that is what we want to fix.
From Lazy to Eager Loading
Eager loading is more efficient than Lazy loading. In Eager loading, when a query is made for one entity, it also includes in the query the ability to fetch all related entities at one time. So far in this series of tutorials, we have been lazy loading almost everything. That’s ok as it gives us the chance to find and correct those points in the code now.
Using with() to reduce queries
Let’s start with ThreadsController.
protected function getThreads(Channel $channel, ThreadFilters $filters)
{
$threads = Thread::latest()->filter($filters);
if ($channel->exists) {
$threads->where('channel_id', $channel->id);
}
$threads = $threads->get();
return $threads;
}As it stands now, when we visit the view to browse all threads, the first thing that happens is we fetch all threads from the database. In the view however, we need to account for a relationship to threads, and that is the relationship of channels. To reduce the reliance on lazy loading, we can make use of the with() eloquent method to eager load the channels with the Threads. Let us update that query to make use of the with() method here:
protected function getThreads(Channel $channel, ThreadFilters $filters)
{
$threads = Thread::with('channel')->latest()->filter($filters);
if ($channel->exists) {
$threads->where('channel_id', $channel->id);
}
$threads = $threads->get();
return $threads;
}We can reload the index view and see that the number of sql queries has been reduces dramatically!
Using Cache to reduce queries
Another way to reduce queries is by clever use of the Cache facade. In AppServiceProvider we configured a view composer that could be refactored.
public function boot()
{
View::composer('*', function ($view) {
$channels = Cache::rememberForever('channels', function () {
return Channel::all();
});
$view->with('channels', $channels);
});
}In the snippet above, we make use of the Cache facade to eliminate the need to query for the channels on every page load. This also helps reduce the number of queries needed.
Reducing Queries When Viewing a Single Thread with Multiple Replies
We have fixed the main index page where we can view all threads at one time. We started with 57 total queries, but finished with only 2 queries. Now you see how useful that with() method is! Moving on to viewing a thread and all associated replies, we are seeing more queries than we probably need to.
Reducing Queries in your View Files
Watch out for calling relationship methods in view files. This is a common source of triggering more queries than you need to. Open up reply.blade.php and look for the calls to $reply->favorites()->count() specifically. These calls are actually triggering database queries. How might we fix this? We can reference those counts as attributes instead of method calls.
<div class="panel panel-default">
<div class="panel-body">
<div class="level">
<h5 class="flex">
<a href="#">{{$reply->owner->name}}</a> said {{ $reply->created_at->diffForHumans() }}
</h5>
<div>
<form method="POST" action="/replies/{{$reply->id}}/favorites">
{{csrf_field()}}
<button type="submit" class="btn btn-primary {{ $reply->isFavorited() ? 'disabled' : '' }}">
{{ $reply->favorites()->count() }} {{ str_plural('Favorite', $reply->favorites()->count()) }}
</button>
</form>
</div>
</div>
</div>
<div class="panel-body">
{{ $reply->body }}
</div>
</div>From call to attribute checking.
<button type="submit" class="btn btn-primary {{ $reply->isFavorited() ? 'disabled' : '' }}">
{{ $reply->favorites_count }} {{ str_plural('Favorite', $reply->favorites_count) }}
</button>We need to set up Eager Loading in the model to facilitate this. Guess what? That’s easy as pie using the withCount() method. Here we highlight the updated Thread Model!
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Thread extends Model
{
protected $guarded = [];
protected static function boot()
{
parent::boot();
static::addGlobalScope('replyCount', function ($builder) {
$builder->withCount('replies');
});
}
public function path()
{
return '/threads/' . $this->channel->slug . '/' . $this->id;
}
public function replies()
{
return $this->hasMany(Reply::class)
->withCount('favorites');
}
public function creator()
{
return $this->belongsTo(User::class, 'user_id');
}
public function channel()
{
return $this->belongsTo(Channel::class);
}
public function addReply($reply)
{
$this->replies()->create($reply);
}
public function scopeFilter($query, $filters)
{
return $filters->apply($query);
}
}We are seeing a nice reduction in queries now.
More Trouble In The View
In reply.blade.php, there is another lazy loading offender by way of the call to $reply->owner->name. What is happening with this is anytime a reply is loaded, additional queries need to be made for the owner of that reply. We want to load the replies, and any related owners, all in one shot. Once again, Eager Loading is here to make that work for us. On the replies method of the Thread Model, we add another call to with() like so.
public function replies()
{
return $this->hasMany(Reply::class)
->withCount('favorites')
->with('owner');
}This will eliminate the need for the call to $reply->owner->name to make any additional queries. The owner is now eager loaded in the Thread model. This reduced another query in our debug bar.
A Lazy Loading Problem in the Reply Model
Recall that we created a nice little function in our Reply model named isFavorited() to help with checking if a user has applied a favorite to a particular reply or not. The code looks like this.
public function isFavorited()
{
return $this->favorites()->where('user_id', auth()->id())->exists();
}It works, and it performs the goal we have. The problem however is that it is using lazy loading. So every time a new reply is added to a thread, that is another query added to that page. We don’t want that.
Eager Loading using protected $with
Want to turn on eager loading automatically? You can do this by populating the $with property in your Model, and providing an array of any related models you want to eager load. This will make the explicit call to with() unnecessary. Let’s see how this works.
In the Reply Model we can populate the $with property with the string of ‘owner’. What this means is that we want to eager load this relationship for every single query. Now, anytime a reply is fetched from the database, the related owner is always available. No need to call any additional with() methods. So if we have this code in Reply.php
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Reply extends Model
{
protected $guarded = [];
protected $with = ['owner'];That means we can *remove* the call to with() in the Thread model like so:
public function replies()
{
return $this->hasMany(Reply::class)
->withCount('favorites');
//->with('owner');
}Eager loading multiple relationships
You can eager load as many relations as you like. How about when we load a reply, we also load the owner in addition to the favorites. All we need to do is populate the $with property like so.
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Reply extends Model
{
protected $guarded = [];
protected $with = ['owner', 'favorites'];With this change, we can update the isFavorited() method on the Reply model. Instead of this:
public function isFavorited()
{
return $this->favorites()->where('user_id', auth()->id())->exists();
}We can now do this:
public function isFavorited()
{
return !!$this->favorites->where('user_id', auth()->id())->count();
}We can also update the Thread model to always load the ‘creator’ relationship. If we feel like anytime we are going to fetch a thread, we are also going to want to have access to the creator, than once again we can use that handy $witch property.
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Thread extends Model
{
protected $guarded = [];
protected $with = ['creator'];Determining Model Count With A Custom Getter
Perhaps you want to go back to keeping the replies() method on the Thread model super simple. Meaning we can also remove the call to withCount(‘favorites’).
public function replies()
{
return $this->hasMany(Reply::class);
//->withCount('favorites');
//->with('owner');
}In order to do this, we can set up a custom getter on the Reply model. Here is how.
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Reply extends Model
{
protected $guarded = [];
protected $with = ['owner', 'favorites'];
public function owner()
{
return $this->belongsTo(User::class, 'user_id');
}
public function favorites()
{
return $this->morphMany(Favorite::class, 'favorited');
}
public function favorite()
{
$attributes = ['user_id' => auth()->id()];
if (!$this->favorites()->where($attributes)->exists()) {
return $this->favorites()->create($attributes);
}
}
public function isFavorited()
{
return !!$this->favorites->where('user_id', auth()->id())->count();
}
public function getFavoritesCountAttribute()
{
return $this->favorites->count();
}
}
Eager Loading ‘channel’ in the Thread model
Let’s also add eager loading for the ‘channel’ relationship in the Thread model like so:
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Thread extends Model
{
protected $guarded = [];
protected $with = ['creator', 'channel'];With this in place, we can modify the getThreads() method in ThreadsController by removing any explicit call to the with() method.
Better Code Organization With A Trait
To wrap up, we’ll use our newfound refactor skills to clean up the Reply model by extracting some code to a dedicated Trait. First off, we can create the Trait file.
Now update the Reply model to use the Favoriteable trait like so:
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Reply extends Model
{
use Favoriteable;Finally, you can make PHP Storm do all the heavy lifting for you by simply using the Pull Members Up tool.
Now you have a nice clean Reply Model and also a nice and clean Trait:
Reply.php
<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Reply extends Model
{
use Favoriteable;
protected $guarded = [];
protected $with = ['owner', 'favorites'];
public function owner()
{
return $this->belongsTo(User::class, 'user_id');
}
}Favoriteable.php
<?php
namespace App;
trait Favoriteable
{
public function favorites()
{
return $this->morphMany(Favorite::class, 'favorited');
}
public function favorite()
{
$attributes = ['user_id' => auth()->id()];
if (!$this->favorites()->where($attributes)->exists()) {
return $this->favorites()->create($attributes);
}
}
public function isFavorited()
{
return !!$this->favorites->where('user_id', auth()->id())->count();
}
public function getFavoritesCountAttribute()
{
return $this->favorites->count();
}
}Run your tests!
Like me, you may be paranoid that with everything we have touched in this tutorial, surely something must now be broken somewhere. Well, we can run our full test suite, and it turns out, everything passes so we are good to go!
vagrant@homestead:~/Code/forumio$ phpunit PHPUnit 6.5.5 by Sebastian Bergmann and contributors. ......................... 25 / 25 (100%) Time: 4.32 seconds, Memory: 12.00MB OK (25 tests, 42 assertions)
How To Fix The N+1 Problem Summary
Making use of modern ORMs is a great way to get a lot done in a little time. The syntax you can use is also very expressive and relatively easy to understand. You do need to be aware however, that you may be triggering too many sql queries if Lazy Loading is in use. In this tutorial, we learned a few ways to make sure we reduce the number of sql queries by making use of Eager Loading.
