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?

Locust

Laravel Debugbar is a great tool to log the queries. It also has many other awesome features. – Stillman

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 ?

For an HTTP request lifecycle, you can enable query log in the `handle` method of some `BeforeAnyDbQueryMiddleware` [middleware][1] and then retrieve the executed queries in the [`terminate`][2] method of the same middleware.

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

Gwenn

If your system uses more than one db connection, you have to specify it, otherwise it might return empty array: \DB::connection('myconnection')->enableQueryLog(); print_r(\DB::connection('myconnection')->getQueryLog()); – Gardell

Post your comment as your answer @DianaR. – Geisha

For Laravel 5.2 see: laravel.com/docs/5.2/database#listening-for-query-events – Barring

How to enable it to log Eloquent "NameController::create();" statement? – Demy

To make your answer more complete please edit your post and add to "2. Where to enable query log?" that to make middleware works you need to add to the /app/Http/Kernel.phpKernel.php file next string to protected $middleware array: \App\Http\Middleware\BeforeAndAfterActionMiddleware::class. Thanks! – Jena

Note that in Laravel 5.4 the DB::listen callback function has a different signature. It's more like this: DB::listen(function($query) { $sql = $query->sql; $bindings = $query->bindings; $time = $query->time; ... }); – Xavier

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)

Hyetal

where does this code go though? (5.4) I've tried controller, model, and looked in middleware, not sure where to execute it before i get the db error. – Demivolt

If you are getting an error when running the query that is stopping execution, the error should tell you what the problem is. If you have errors turned off, you can check the error log in /storage/log/laravel or something like that. (I'm not at my computer at the moment) If you are saying you're getting an error running the code I suggested in my answer, make sure you're including the DB facade wherever you are running the code. Not sure what you are trying to do, but the controller sounds like the most correct of the options you mentioned. (I usually run queries in seperate helper classes) – Hyetal

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.

Ulterior

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.

Piste

A bit dirty, but +1 for the $query->bindings and $query->time hints – Commandeer

Neat! Using this shows the results in the view, right where the query is occurring! – Haematogenous

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);
    }
);

Sulky

For older Laravel, I added my solution to https://mcmap.net/q/53641/-how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string – Unclog

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'

Strongwilled

thanks brother simple enough – Bastogne

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

Harlan

simply awesome in laravel 8 as well – Bastogne

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); });

Mentor

Answer 9

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.

Micropathology

Answer 10

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

Carr

Answer 11

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);

Haydeehayden

which part should be placed into middleware ? which in routes ? – Galyak

Answer 12

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.

Transmogrify

how this line of code is works? if(stripos($query, 'insert into '.$LOG_TABLE_NAME.'')===false){ – Carr

@MohamedRaza prevents the recursion. otherwise, the "INSERT INTO yourlogtable" query itself will trigger this method, and generate a new insert statement, and so on. – Transmogrify

yes which is a endless loop, any how if statement also doesn't break the loop – Carr

Answer 13

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));
        });
    }
}

Cleres

Answer 14

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"

Trichloride

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.

Judicial

It's a bad idea, to edit vendor files. They must be kept original. – Kalasky

@Kalasky Yes one must not edit vendor files but to get the exact query we have edit this code for time being then we can change it back. – Judicial