DEV Community

Cover image for Debugging queries - lazy vs eager loading in Laravel
costica
costica

Posted on • Edited on

Debugging queries - lazy vs eager loading in Laravel

Nowadays, frameworks bring a lot to the table. They bring so much, we often neglect what's actually happening under the hood and just take it for granted.
Don't get me wrong, I am not advocating against frameworks. I love them and for a long time I thought that using frameworks without giving a **** about their internals is fine.

I relied on magic. But, as someone old and bald keeps repeating:

There is no such thing as magic

So I started to pay more attention to what my code does ( or does not , hihi ).

Enough intro, let's cut to the chase. We want to list some posts and for each post its comments. One way to go at this is :

@foreach($posts as $post)
    <h3>{{$post->name}}</h3>
    @foreach($post->comments as $comment)
        <p>
            {{$comment->text}}
        </p>
    @endforeach
@endforeach
Enter fullscreen mode Exit fullscreen mode

The relationships defined in my models are :

class Post extends Model
{
    protected $table = "posts";
    public function comments()
    {
        return $this->hasMany(Comment::Class);
    }
}

class Comment extends Model
{
    protected $table = "comments";
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}
Enter fullscreen mode Exit fullscreen mode

Simple eloquent relationships. A lot of magic under the hood, so we can write this

    // Eager loading
    $posts = Post::with("comments")->get();
Enter fullscreen mode Exit fullscreen mode

Or even simpler, this

    // Lazy loading ( the actual query which brings the comments for a post is called later ) 
     $posts = Post::get();
Enter fullscreen mode Exit fullscreen mode

Remember, it is the same code that displays them:

     @foreach($posts as $post)
         @foreach($post->comments as $comment)
         ...
         @endforeach
     @endforeach
Enter fullscreen mode Exit fullscreen mode

So, what is actually happening ?

Well, in the first case ( eager loading ), the following 2 ( TWO ) queries get executed :

    DB::enableQueryLog();
    $posts = Post::with("comments")->get();
    $query_dump = DB::getQueryLog();

    // dump value : 
    $query_dump === array:2 [
     0 => array:3 [
       "query" => "select * from `posts`"
       "bindings" => []
       "time" => 1.47
     ]
     1 => array:3 [
       "query" => "select * from `comments` where `comments`.`post_id` in (1, 2, 3)"
       "bindings" => []
       "time" => 0.2
     ]
   ]
Enter fullscreen mode Exit fullscreen mode

These are the queries that Laravel does under the hood.

Now, for the lazy-loading :

    DB::enableQueryLog();
    $posts = Post::get();
    $query_dump = DB::getQueryLog();

    // dump value : 
    $query_dump === array:1 [
      0 => array:3 [
        "query" => "select * from `posts`"
        "bindings" => []
        "time" => 1.71
      ]
    ]
Enter fullscreen mode Exit fullscreen mode

Only 1 query. But in view, for every post we get this query logged:

    @foreach($posts as $post)
        @php
            \DB::enableQueryLog();
        @endphp

        <h3>{{$post->name}}</h3>

        @foreach($post->comments as $comment)
            <p>
                {{$comment->text}}
            </p>
        @endforeach
        @php
            $query_dump = \DB::getQueryLog();
            dump($query_dump);
        @endphp
    @endforeach

    // dump value : 
    $query_dump === array:1 [
      0 => array:3 [
        "query" => "select * from `comments` where `comments`.`post_id` = ? and `comments`.`post_id` is not null"
        "bindings" => array:1 [
          0 => 1
        ]
        "time" => 0.2
      ]
    ]
Enter fullscreen mode Exit fullscreen mode

Skipping the talk about design patterns and WHERE some code should be. Tl;dr is that queries SHOULD NOT be in the view.

So we have 1 initial query and 3 inside the view. For this simple example, we already have doubled the number of queries.

Now imagine having 100 posts, and calling things like $post->image->path and $post->users_who_like_it aaaaaaaaaaaaaaand awful nested $post->users_who_like_it->image->path.

$post->image->path is not nested, it's a simple join between post and its image -> path is the property of the image

$post->users_who_like_it->image->path is nested : post is "joined" with users , and then each user with its image

Yeah, nesting on this would make it even worse.

Now, you can do the math and if you think this is not an issue, go ahead and lazy load everything. It is, in the end, your decision. But now, hopefully, an informed one.

Penny for your thoughts || suggestions || feedback !

Cheers : )

Top comments (1)

Collapse
 
tylerlwsmith profile image
Tyler Smith

This was really helpful for me. I just started building this out in Laravel to see the same thing myself when I stumbled across this article. Thank you for writing!