How To Avoid Duplicate Database Queries In Your Laravel Application

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.

Removing Duplicate Queries Via Eager Loading

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.