Get SQL Query in Laravel

Sometimes retrieving data in Laravel can return incorrect results or unexpected values. To debug or optimize these cases, we often need to get SQL query in Laravel and inspect the exact query executed. Laravel provides multiple ways to convert Eloquent queries into raw SQL queries, making it easier to analyze and troubleshoot database operations.

• Using Laravel Eloquent methods
• 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 the 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 a normal query but instead of using the get() method we will use the 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 a query.

Another method is used to get stored query logs which is getQueryLog(). Let’s take an example of 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 the first method, 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 the first method gets a row query without executing while into second method executes a query and logs into the background.

You can also verify your Laravel setup by learning how to check database connection in Laravel for smooth application performance.