Get SQL query in Laravel

Sometimes we face issues in getting data or getting the wrong data. We can resolve this type of issue using row SQL queries but for that, we need to convert the eloquent queries into row queries. Laravel provides two ways to get row queries.

  1. Using Laravel Eloquent methods
  2. Using the Laravel Query Log

Using Laravel Eloquent methods

This method to get the query of an Eloquent call is by using the toSql() method. This method returns the query without running it. but this method is not too useful with complex queries where a large set of relations or sub-queries are performing together.

The toSql() function will automatically convert model query into a row SQL query. Let's take an example to understand it.

$users = User::select('id','name','email')
    ->where('created_at', '<', now()->subYear())
    ->with('data')
    ->orderBy('email', 'asc')
    ->limit(10)
    ->skip(5)
    ->toSql();

Here, we write all code the same as normal query but instead of using get() method we will use toSql() method and it will convert the Eloquent query into a row SQL query.

Output :

select `id`, `name`, `email` from `users` where `created_at` < ? order by `email` asc limit 10 offset 5

Using the Laravel Query Log

In this method, we enable the Laravel framework to log every query executed between code blocks and get it using its method. The DB facade provides database methods one of them is enableQueryLog() which will tell the framework to start logging query.

Another method is used to get stored query logs which is getQueryLog(). Let's take an example for this method.

DB::enableQueryLog();

$user = User::select('id','name','email')
    ->latest()
    ->get();

$users = User::select('id', 'name')->orderBy('email',"DESC")->get();

dd(DB::getQueryLog());

Output:

array:2 [â–¼
  0 => array:3 [â–¼
    "query" => "select `id`, `name`, `email` from `users` order by `created_at` desc"
    "bindings" => []
    "time" => 2.36
  ]
  1 => array:3 [â–¼
    "query" => "select `id`, `name` from `users` order by `email` desc"
    "bindings" => []
    "time" => 0.33
  ]

Here, before executing any query we have enabled query log and then executed multiple queries and at last we have printed all queries using die dump.

Conclusion

Here, both methods return row queries but in the first method, it will return only one query while we can use the second method to get all queries between code blocks. Another major difference between both methods is that first method get row query without executing while into second method executes a query and logs into the background.