How to Get the Query Executed in Laravel 5? DB::getQueryLog() Returning Empty Array

Question

I'm trying to view the log for a query, but DB::getQueryLog() is just returning an empty array:

$user = User::find(5);
print_r(DB::getQueryLog());

Result

Array
(
)

How can I view the log for this query?

Answer 1

By default, the query log is disabled in Laravel 5: https://github.com/laravel/framework/commit/e0abfe5c49d225567cb4dfd56df9ef05cc297448

You will need to enable the query log by calling:

DB::enableQueryLog();

// and then you can get query log

dd(DB::getQueryLog());

or register an event listener:

DB::listen(
    function ($sql, $bindings, $time) {
        //  $sql - select * from `ncv_users` where `ncv_users`.`id` = ? limit 1
        //  $bindings - [5]
        //  $time(in milliseconds) - 0.38 
    }
);  

Some Tips

1. Multiple DB connections

If you have more than one DB connection you must specify which connection to log

To enables query log for my_connection:

DB::connection('my_connection')->enableQueryLog();

To get query log for my_connection:

print_r(
   DB::connection('my_connection')->getQueryLog()
);

2. Where to enable query log ?

class BeforeAnyDbQueryMiddleware
{
    public function handle($request, Closure $next)
    {
        DB::enableQueryLog();
        return $next($request);
    }

    public function terminate($request, $response)
    {
        // Store or dump the log data...
        dd(
            DB::getQueryLog()
        );
    }
}

A middleware's chain will not run for artisan commands, so for CLI execution you can enable query log in the artisan.start event listener.

For example you can put it in the bootstrap/app.php file

$app['events']->listen('artisan.start', function(){
    \DB::enableQueryLog();
});

3. Memory

Laravel keeps all queries in memory. So in some cases, such as when inserting a large number of rows, or having a long running job with a lot of queries, this can cause the application to use excess memory.

In most cases you will need the query log only for debugging, and if that is the case I would recommend you enable it only for development.

if (App::environment('local')) {
    // The environment is local
    DB::enableQueryLog();
}

References

• https://laravel.com/docs/5.0/database#query-logging

Answer 2

If all you really care about is the actual query (the last one run) for quick debugging purposes:

DB::enableQueryLog();

# your laravel query builder goes here

$laQuery = DB::getQueryLog();

$lcWhatYouWant = $laQuery[0]['query']; # <-------

# optionally disable the query log:
DB::disableQueryLog();

do a print_r() on $laQuery[0] to get the full query, including the bindings. (the $lcWhatYouWant variable above will have the variables replaced with ??)

If you're using something other than the main mysql connection, you'll need to use these instead:

DB::connection("mysql2")->enableQueryLog();

DB::connection("mysql2")->getQueryLog();

(with your connection name where "mysql2" is)

Answer 3

You need to first enable query logging

DB::enableQueryLog();

Then you can get query logs by simply:

dd(DB::getQueryLog());

It would be better if you enable query logging before application starts, which you can do in a BeforeMiddleware and then retrieve the executed queries in AfterMiddleware.

Answer 4

Put this on routes.php file:

\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
    echo'<pre>';
    var_dump($query->sql);
    var_dump($query->bindings);
    var_dump($query->time);
    echo'</pre>';
});

Submitted by msurguy, source code in this page. You will find this fix-code for laravel 5.2 in comments.

Answer 5

Apparently with Laravel 5.2, the closure in DB::listen only receives a single parameter.

So, if you want to use DB::listen in Laravel 5.2, you should do something like:

DB::listen(
    function ($sql) {
        // $sql is an object with the properties:
        //  sql: The query
        //  bindings: the sql query variables
        //  time: The execution time for the query
        //  connectionName: The name of the connection

        // To save the executed queries to file:
        // Process the sql and the bindings:
        foreach ($sql->bindings as $i => $binding) {
            if ($binding instanceof \DateTime) {
                $sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
            } else {
                if (is_string($binding)) {
                    $sql->bindings[$i] = "'$binding'";
                }
            }
        }

        // Insert bindings into query
        $query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);

        $query = vsprintf($query, $sql->bindings);

        // Save the query to file
        $logFile = fopen(
            storage_path('logs' . DIRECTORY_SEPARATOR . date('Y-m-d') . '_query.log'),
            'a+'
        );
        fwrite($logFile, date('Y-m-d H:i:s') . ': ' . $query . PHP_EOL);
        fclose($logFile);
    }
);

Answer 6

Use toSql() instead of get() like so:

$users = User::orderBy('name', 'asc')->toSql();
echo $users;
// Outputs the string:
'select * from `users` order by `name` asc'

Answer 7

For laravel 5.8 you just add dd or dump.

Ex:

DB::table('users')->where('votes', '>', 100)->dd();

or

DB::table('users')->where('votes', '>', 100)->dump();

reference: https://laravel.com/docs/5.8/queries#debugging

Answer 8

(Laravel 5.2) I find the simplest way is just to add one code line to monitor the sql queries:

\DB::listen(function($sql) {var_dump($sql); });

Answer 9

In continuing of the Apparently with Laravel 5.2, the closure in DB::listen only receives a single parameter... response above : you can put this code into the Middleware script and use it in the routes.

Additionally:

use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$log = new Logger('sql');
$log->pushHandler(new StreamHandler(storage_path().'/logs/sql-' . date('Y-m-d') . '.log', Logger::INFO));

// add records to the log
$log->addInfo($query, $data);

Answer 10

This code is for:

• Laravel 5.2
• Log the statements into the mysql database

Here is the code, which is based on @milz 's answer:

    DB::listen(function($sql) {
        $LOG_TABLE_NAME = 'log';
        foreach ($sql->bindings as $i => $binding) {
            if ($binding instanceof \DateTime) {
                $sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
            } else {
                if (is_string($binding)) {
                    $sql->bindings[$i] = "'$binding'";
                }
            }
        }
        // Insert bindings into query
        $query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);
        $query = vsprintf($query, $sql->bindings);
        if(stripos($query, 'insert into `'.$LOG_TABLE_NAME.'`')===false){
            $toLog = new LogModel();
            $toLog->uId = 100;
            $toLog->sql = $query;
            $toLog->save();
        }
    });

The core is the if(stripos... line, which prevents the recursion of inserting the insert into log sql statement into database.

Answer 11

Suppose you want to print the SQL query of the following statements.

$user = User::find(5);

You just need to do as follows:

DB::enableQueryLog();//enable query logging

$user = User::find(5);

print_r(DB::getQueryLog());//print sql query

This will print the last executed query in Laravel.

Answer 12

I think the answer located in this article: https://arjunphp.com/laravel-5-5-log-eloquent-queries/

is fast and simple to achieve query logging.

You just have to add to the AppServiceProvider in the boot method a callback to listen to DB queries:

namespace App\Providers;

use DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        DB::listen(function($query) {
            logger()->info($query->sql . print_r($query->bindings, true));
        });
    }
}

Answer 13

Add this function to your helper file and simply call.

function getRawQuery($sql){
        $query = str_replace(array('?'), array('\'%s\''), $sql->toSql());
        $query = vsprintf($query, $sql->getBindings());     
        return $query;
}

Output: "select * from user where status = '1' order by id desc limit 25 offset 0"

Answer 14

Query Execution

\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {          
            $sql = $query->sql; 
            $time = $query->time;
            $connection = $query->connection->getName();
 
            Log::debug('query : '.$sql);
            Log::debug('time '.$time);
            Log::debug('connection '.$connection);
        });

Query

StaffRegister::all();

Output

[2021-03-14 08:00:57] local.DEBUG: query : select * from `staff_registers`  
[2021-03-14 08:00:57] local.DEBUG: time 0.93  
[2021-03-14 08:00:57] local.DEBUG: connection mysql  

complete structure

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\Log;
use App\Models\StaffRegister;

class AuthController extends Controller
{
   public function index(){
   
       \Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
      
           $sql = $query->sql; 
           $time = $query->time;
           $connection = $query->connection->getName();

           Log::debug('query : '.$sql);
           Log::debug('time '.$time);
           Log::debug('connection '.$connection);
       });

       $obj = StaffRegister::all(); 
    
       return $obj;
   }
}

Accurate Method For GET REPOSNSE

Answer 15

For laravel 5 and onwards using only DB::getQueryLog() , will not do. BY default in this the value of

 protected $loggingQueries = false;

change it to

protected $loggingQueries = true; 

in the below file for logging query.

/vendor/laravel/framework/src/illuminate/Database/Connection.php 

And then we can use the DB::getQueryLog() where you want to print the query.