We know that the majority of users prefer faster and more responsive applications. Nobody likes to be in a line waiting for their food to be ready, or in our case, a request result to finally return.

One of the major factors affecting the application performance is the improper design of the database and poorly written inefficient queries.

Although some would argue that in today's time it is cheaper than ever to rent premium cloud servers, we still need to pay attention to this matter and optimize our queries as much as we can.

In Laravel, we are almost always utilizing Eloquent ORM which is one of the most loved features of the framework.

For some that don't know ORM stands for object-relational mapping and as the name suggests, it allows us to interact with relational data using object-oriented approaches via models.

However, sometimes we may end up creating some messy queries as we are not writing raw SQL all that often.

Let's see how, really simply, we can get an SQL representation of what eloquent is really doing with its fancy models and methods.

DB::enableQueryLog();

$articles = Article::where('published', true)
    ->orderBy('created_at', 'desc')
    ->get();

dd(DB::getQueryLog());

This is something simple that you have probably done hundreds of times. This produces the following

array:1 [▼
  0 => array:3 [▼
    "query" => "select * from `articles` where `published` = ? order by `created_at` desc""bindings" => array:1 [▼
      0 => true
    ]
    "time" => 4.13
  ]
]

Here we can see the exact query executed by Eloquent to retrieve our articles. Let's try something else now. Let's say that we have tags on our articles. It's a many-to-many relation. If we try to display tags of our article with an approach of just doing

@foreach($article->tags as $tag)
   <p>{{ $tag->name }}</p>
@endforeach

we would run into the N+1 problem as relational data is lazy loaded meaning that it is not actually loaded until we first try to access it and to actually retrieve it we would have to execute as many queries as there are articles to retrieve associated tags.

1 is for the initial query of getting records and N is the number of additional queries for related data.

In order to prevent this problem, we can eager load the relation like this

DB::enableQueryLog();

$articles = Article::with('tags')
    ->where('published', true)
    ->orderBy('created_at', 'desc')
    ->get();

and we get the following query

array:2 [▼
  0 => array:3 [▼
    "query" => "select * from `articles` where `published` = ? order by `created_at` desc"
    "bindings" => array:1 [▼
      0 => true
    ]
    "time" => 5.43
  ]
  1 => array:3 [▼
    "query" => "select `tags`.*, `article_tag`.`article_id` as `pivot_article_id`, `article_tag`.`tag_id` as `pivot_tag_id` from `tags` inner join `article_tag` on `tags`.`id` = `article_tag`.`tag_id` where `article_tag`.`article_id` in (3, 5, 6, 7, 11, 18, 19, 20, 22, 24, 27, 28, 31) ◀""bindings" => []
    "time" => 11.18
  ]
]

With eager loading, we only do two queries. Get all articles and with those article ids get related tags, simple!

Although eager loading is awesome in some situations, it is not the point of this article. The point was to show how useful it is to see the actual query executing under the hood of the application.

We can all read how some things are supposed to be working but only when we see in-depth how it operates can we truly understand it.

Now you can log all the queries that you wondered about how they work.