How to get raw SQL query output from the query builder in laravel?

Eloquent is a fantastic database query builder and ORM system that comes in laravel.

Many times while debugging the query which is generated by query builder we need raw SQL query. There are multiple ways to get a raw SQL query.

For example, the query generated by query builder is:

Product::select('*')->where('status', 'active')->get();

Now, let’s see how can we get the raw SQL query of the above statement.

1) Using toSql():

  • To use toSql() replace the closing ->get() with ->toSql() and print the result with dd().
$products = Product::select('*')->where('status', 'active')->toSql();
dd($products);

Output:

"select * from `products` where `status` = ?"

2) Using dd():

  • dd() prints row SQL query with query bindings and then stop executing the request.
Product::select('*')->where('status', 'active')->dd();

Output:

"select * from `products` where `status` = ?"

array:1 [▼
0 => "active"
]

3) Using dump():

  • dump() also prints row SQL query with query bindings but allow the request to keep executing.
Product::select('*')->where('status', 'active')->dump();

Output:

"select * from `products` where `status` = ?"

array:1 [▼
0 => "active"
]

4) Listening For Query Events:

  • If you would like to receive each SQL query executed by your application, you may use the listen method.
  • We can get raw SQL query, query bindings and the time that a query took to run using the listen method.

To use DB::listen() open your AppServiceProvider.php and write the below code:

<?php

namespace App\Providers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function register()
    {
    }

    public function boot()
    {
        DB::listen(function ($query) {
            // $query->sql
            // $query->bindings
            // $query->time

            echo $query->sql;
            echo "<br>";
            print_r($query->bindings);
            echo "<br>";
            echo $query->time;
        });
    }
}

Output:

Here we wil get raw SQL query, array of query bindings and query execution time.

select * from `products` where `status` = ?
Array ( [0] => active )
12.45

Leave a Reply

avatar
  Subscribe  
Notify of