July, 06 2020
Recently, I was working on an internal project for my company. This project is an educational web app that has a lot to do with questions, the source and type of question and the feature that I am working on is to displays all the questions that belong to a subject on the frontend end.
I have a Question, Source and Type models. The Question
model has one Source
and has one Type
. My Question Model class looks like this.
<?php
// Question Model
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Question extends Model
{
public function source()
{
return $this->hasOne(Source::class, 'id', 'source_id');
}
public function type()
{
return $this->hasOne(Type::class, 'id', 'type_id');
}
}
And here is the controller class that displays the questions:
<?php
// Question Controller
namespace App\Http\Controllers;
use App\Models\Question;
class QuestionController extends Controller
{
public function index()
{
return view('question-list', [
'questions' => Question::paginate(100)
]);
}
}
As we loop through these 100 questions inside our blade view, we can access the related model(source and type) which will result to duplicate queries.
<!-- question.blade.php -->
@foreach($questions as $question)
<div class="col-sm-12">
Question Id: {{question->id}} <br>
Question Source: {{question->source->name}} <br>
Question Type: {{question->type->title}} <br> <hr>
</div>
@endforeach
Using Laravel Debugbar, I found out that I have 200 extra database queries which are duplicates, that runs whenever I access the source
and type
relationships defined inside the Question
model.
I solved this duplicate queries(N+1 problem) with eager loading. With eager loading, the database query will run once and pull everything we need.
There are two ways we can eager load an eloquent relationship. The first is by overriding the $with
property which is an array
while the second is by calling the with()
method on your eloquent model instance.
I am going to show you this two methods works but let me start by explaining the with()
method which is what I used in my own case.
Solving the problem, I passed the two related models I want to eager load to the with('source', 'type')
method before calling the paginate()
method.
<?php
// Question Controller with eager loading
namespace App\Http\Controllers;
use App\Models\Question;
class QuestionController extends Controller
{
public function index()
{
return view('question-list', [
'questions' => Question::with('source', 'type')->paginate(100)
]);
}
}
With my two related models eager loaded, my database queries dropped down to 4 queries with no duplicates.
Like I mentioned earlier, you can also eager load a relationship by overriding the $with
property of your eloquent model.
In my case, I will override the $with
property and add to source
and type
as the array items inside my Question
model.
<?php
// Question Model
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Question extends Model
{
protected $with = ['source', 'type'];
}
This is the same as what we did inside the index()
method of our QuestionController
.
You can check the Laravel documentation for more details about eager loading.
Be the first to hear about anything I publish, launch, or think is helpful for you. Subscribe here
Litehost is a web hosting platform for PHP & Laravel developers with Composer, Git, PHP & CLI pre-installed. Try it now