Debugging queries - lazy vs eager loading in Laravel
Tags: laravel, php, mysql, webdev
Published: 2019-03-14 21:05:48

---
title: Debugging queries - lazy vs eager loading in Laravel
published: true
description: Understanding lazy and eager loading in Laravel and query dumping to see what happens under the hood
cover_image: https://thepracticaldev.s3.amazonaws.com/i/vdaisdlriejked6iwvl1.jpg
tags: laravel, php, mysql, webdev
---

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 : 

```php
@foreach($posts as $post)
    <h3>{{$post->name}}</h3>
    @foreach($post->comments as $comment)
        <p>
            {{$comment->text}}
        </p>
    @endforeach
@endforeach
```

The relationships defined in my models are : 
```php
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);
    }
}
```

Simple eloquent relationships. A lot of magic under the hood, so we can write this
 ```php
    // Eager loading
    $posts = Post::with("comments")->get();
 ```
 
 Or even simpler, this 
```php
    // Lazy loading ( the actual query which brings the comments for a post is called later ) 
     $posts = Post::get();
```

Remember, it is the same code that displays them:
```php
     @foreach($posts as $post)
         @foreach($post->comments as $comment)
         ...
         @endforeach
     @endforeach
``` 

So, what is actually happening ?

Well, in the first case ( eager loading ), the following 2 ( TWO ) queries get executed : 
```php
    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
     ]
   ]
```

These are the queries that Laravel does under the hood. 

Now, for the lazy-loading : 
```php
    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
      ]
    ]
```

Only 1 query. But in view, for every post we get this query logged: 
```php
    @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
      ]
    ]
```

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 <code>$post->image->path</code> and <code>$post->users_who_like_it</code> aaaaaaaaaaaaaaand awful nested <code>$post->users_who_like_it->image->path</code>.  
> $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 : )