Eager loading and lazy loading

Eager loading and lazy loading

Eager loading and lazy loading are two techniques we have available to retrieve data related to working with Eloquent models. And we have to know them in detail to use techniques that best suit our needs; there is no one technique better than the other, both are used to optimize application performance by reducing the number of database queries required to obtain related data. Let's get to know them in detail.

Lazy Loading

Also known as “on-demand loading” or “lazy loading”; this is the default behavior in Eloquent that is used when using foreign relations.

The way this technique works is that when obtaining a collection of related data (meaning a list of records coming from a relationship, for example, the list of publications given the category), Eloquent only recovers the data from the database in the moment you request them. That is, for each access to a related record, a separate query is executed to the database. This usually brings with it the famous N+1 type problem, where N+1 database queries are executed in the same task.

Already in our dashboard module we have this problem, on the one hand, we have the main query:

app\Http\Controllers\Dashboard\PostController.php

public function index()
{
    if(!auth()->user()->hasPermissionTo('editor.post.index')){
        return abort(403);
    }

    $posts = Post::paginate(10);
    return view('dashboard/post/index', compact('posts'));
}

And from the view, we reference the category, by default, Laravel uses the lazy loading technique to obtain the related data therefore, every time a query is made, an additional query will be made, from the list, we are obtaining the category and with this an additional query for each post on the page:

resources\views\dashboard\post\index.blade.php

@foreach ($posts as $p)
     ****
     <td>
        {{ $p->category->title }}
***

Which is the N+1 problem, where N in our example is the size of the page, about 10 representing the categories obtained from the post and 1 is the main query to obtain the paginated data.

Luckily, Laravel in modern versions allows you to detect this problem very easily through the following configuration:

app\Providers\AppServiceProvider.php

<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Model;
***
class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        Model::preventLazyLoading(app()->isProduction());
    }
}

With the AppServiceProvider we can load essential classes from our project to integrate them into the application.

So, if we now try to access the previous page, we will see an error on the screen like the following:

Attempted to lazy load [category] on model [App\Models\Post] but lazy loading is disabled.

The N+1 problem detection system in Laravel is not perfect, since if we only had 1 level pagination, the previous exception would not occur.

With an additional trick, we can see the queries made to resolve a client request:

routes/web.php

DB::listen(function ($query){
    echo $query->sql;
  //  Log::info($query->sql, ['bindings' => $query->bindings, 'time' => $query->time]);
});

We can also use the debugbar extension, but we will see this in the next chapter. If you enable the previous script, you will see that more than 15 queries occur, one of them for the session of the authenticated user, permissions and roles, the one for the posts, and 10 for categories if you have a 10 level pagination, this is great to detect the problem but with the drawback that our detail page for the category no longer works, to correct it, we are going to introduce the following topic.

Let's create another example, let's use the list of posts that we have in the category:

app\Models\Category.php

class Category extends Model
{
   ***
    function posts() {
        return $this->hasMany(Post::class);
    }
}

If from the view we obtain the relationship:

resources\views\dashboard\category\index.blade.php

@foreach ($categories as $c)
    ***
        <td>
            {{ $c->posts }}

We will see the previous exception, so, we get the posts along with the categories:

app\Http\Controllers\Dashboard\CategoryController.php

$categories = Category::with('posts')->paginate(10);

The problem with this scheme is that it will bring all the posts associated with a category, and a post is a somewhat heavy relationship, since it contains the content column with all the HTML content, and if we add the 10 categories in the list the problem multiplies.

There are several ways in which we can specify the columns that we want to obtain from the secondary relationship:

$posts = Post::with('category:id,title')->paginate(10);
$posts = Post::with(['category' => function($query){
   // $query->where('id',1);
   $query->select('id','title');
}])->paginate(10);

Although these schemes are not supported by the list of category posts:

$categories = Category::with('posts:id,title')->paginate(10);
$categories = Category::with(['posts' => function($query){
    // $query->where('id',1);
    $query->select('id','title');
}])->paginate(10);

At the moment, we cannot solve the problem and with this the exception since to do so we need to either change the request so that it uses JOINs, or present the next topic which is Eager Loading that we will see below.

Eager Loading

With this process we can perform all the operations in a single query, if we go to the previous example, which we have N+1 queries to the database, we will only perform a single query and with this, improve the performance of the application, to do so, we must specify the relationship when performing the main query:

app\Http\Controllers\Dashboard\PostController.php

$posts = Post::with(['category'])->paginate(10);

If we go to our category detail page, we will see that it works correctly.

This function has many implementations, for example, if we have a nested relationship:

class Tutorial extends Model
{
    ***
}

We can also define in the model the use of this technique by default:

class Post extends Model
{
    protected $with = ['category'];
}

We can extend the with() method in more complex relationships, like the following one where we have a two-level relationship:

class Tutorial extends Model
{
  ***
    public function sections()
    {
        return $this->hasMany(Tutorial::class);
    }
}

class TutorialSection extends Model
{
  ***
    public function tutorial()
    {
        return $this->belongsTo(Tutorial::class);
    }
    public function classes()
    {
        return $this->hasMany(Tutorial::class);
    }
}
class TutorialSectionClass extends Model
{
    ***
    public function tutorialSection()
    {
        return $this->belongsTo(TutorialSection::class);
    }
}

We can make queries in the following way, indicating more than one relationship to obtain:

$posts = Post::with(['categories','tags'])->get();

Or if you want to place a condition on some of the relationships, you can implement a callback like this:

Tutorial::with('sections')->with(['sections.classes' => function ($query) {
     $query->where('posted', 'yes');
     $query->orderBy('orden');
    }])->where('posted', 'yes')->find($tutorial->id);
}

Conclusion

It is important to mention that there is no better technique than the other, since it all depends on what you want to do, but we can simplify it in the following way, if we have a collection of records related through an FK as in the previous example and not If you are going to use the foreign relationship, the technique you should use would be lazy loading, but if you are going to use the collection with the related records, you should use eager loading.

 

Finally, for each relationship specified in the with only adds one additional query, also remember to specify the columns as much as possible when obtaining the relations.

12-06-2024 - Andrés Cruz

En español

• Category
• /
• Laravel
• /
• Eager loading and lazy loading