Article image

HOW TO GET AN OUTPUT OF A RAW SQL QUERY STRING IN LARAVEL

Laravel -

Sep 27 2019

Dino Numic

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 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 now ORM stands for object-relational mapper 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 it's 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 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();

dd(DB::getQueryLog());

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.