Get SQL Query In Laravel

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

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

Using Laravel Eloquent methods

The 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 not too useful with complex queries where large set of relation or sub-queries are performing together.

The toSql() function will automatically convert model query into row SQL query. Let's take 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 same as normal query but instand of using get() method we will use toSql() method and it will convert Eloquent query into 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 Laravel framework to log every query executed between code block and get it using it's method. The DB facade provides database methods one of them is enableQueryLog() which will tell framework to start logging query.

The another method is used to get stored query log which is getQueryLog(). Let's take 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 returns row queries but in first method, it will return only one query while we can use second method to get all query between code block. Another major difference between both method is that first method get row query without executing while into second method it execute query and log into background.