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?

[Laravel Debugbar](https://github.com/barryvdh/laravel-debugbar) is a great tool to log the queries. It also has many other awesome features. — totymedli, Jan 05 '17 at 23:55

Marty Aghajanyan · Accepted Answer · 2020-08-11T22:57:41.333

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

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());` — Diana R., Jun 23 '15 at 14:19
For Laravel 5.2 see: https://laravel.com/docs/5.2/database#listening-for-query-events — Dmitri Chebotarev, Aug 11 '16 at 14:41
How to enable it to log Eloquent "NameController::create();" statement? — Rubén Ruíz, Jan 22 '17 at 20:27
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! — Vaha, Jun 12 '17 at 08:52
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; ... }); ` — racl101, Jul 24 '17 at 17:14

score 45 · Answer 2 · edited Oct 21 '19 at 09:35

45

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)

edited Oct 21 '19 at 09:35

Salman Zafar

3,844
5
20
43

answered Jun 13 '16 at 12:38

Skeets

4,476
2
41
67

1

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. – blamb Jul 19 '17 at 22:46
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) – Skeets Jul 19 '17 at 23:58

score 18 · Answer 3 · edited Nov 06 '19 at 12:36

18

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.

edited Nov 06 '19 at 12:36

Salman Zafar

3,844
5
20
43

answered Apr 15 '15 at 18:42

Vineet Garg

346
2
7

score 17 · Answer 4 · edited Jan 22 '17 at 21:14

17

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.

edited Jan 22 '17 at 21:14

MJH

2,301
7
18
20

answered Jan 22 '17 at 20:42

Rubén Ruíz

453
4
9

A bit dirty, but +1 for the $query->bindings and $query->time hints – Paolo Stefan Oct 09 '19 at 10:32
Neat! Using this shows the results in the view, right where the query is occurring! – Charles Wood Dec 30 '19 at 21:31

score 13 · Answer 5 · answered Jan 06 '16 at 16:49

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

For older Laravel, I added my solution to https://stackoverflow.com/a/44920198/3823826 — Csongor Halmai, Jul 06 '17 at 01:07

doncadavona · Answer 6 · 2021-10-14T15:40:17.483

11

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'

edited Oct 14 '21 at 15:40

answered Feb 15 '19 at 05:55

doncadavona

7,162
9
41
54

1

thanks brother simple enough – justnajm Oct 13 '21 at 16:53

score 9 · Answer 7 · answered Jul 23 '19 at 06:08

9

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

answered Jul 23 '19 at 06:08

larp

1,017
1
14
23

simply awesome in laravel 8 as well – justnajm Oct 13 '21 at 16:52

score 2 · Answer 8 · edited Oct 21 '19 at 09:36

2

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

edited Oct 21 '19 at 09:36

Salman Zafar

3,844
5
20
43

answered Aug 04 '16 at 17:12

ChrisH

127
1
11

score 2 · Answer 9 · answered Mar 14 '21 at 08:12

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

score 1 · Answer 10 · edited May 23 '17 at 12:26

1

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

edited May 23 '17 at 12:26

Community

1
1

answered Feb 08 '16 at 10:27

Saint Father

21
3

which part should be placed into middleware ? which in routes ? – user1016265 Feb 22 '16 at 09:09

score 1 · Answer 11 · answered May 08 '16 at 09: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.

how this line of code is works? `if(stripos($query, 'insert into `'.$LOG_TABLE_NAME.'`')===false){` — Mohamed Raza, Mar 14 '21 at 09:44
@MohamedRaza prevents the recursion. otherwise, the "INSERT INTO yourlogtable" query itself will trigger this method, and generate a new insert statement, and so on. — ch271828n, Mar 14 '21 at 10:08
yes which is a endless loop, any how if statement also doesn't break the loop — Mohamed Raza, Mar 14 '21 at 10:23

score 1 · Answer 12 · answered Nov 20 '19 at 05:19

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.

Javier Núñez · Answer 13 · 2019-10-21T12:48:06.843

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

score -1 · Answer 14 · answered Oct 06 '20 at 13:18

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"

score -3 · Answer 15 · edited Oct 21 '19 at 09:35

-3

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.

edited Oct 21 '19 at 09:35

Salman Zafar

3,844
5
20
43

answered Apr 11 '17 at 06:10

Rupali Pemare

540
1
9
24

1

It's a bad idea, to edit `vendor` files. They must be kept original. – shukshin.ivan Aug 02 '17 at 08:45
@shukshin.ivan 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. – Rupali Pemare Oct 25 '17 at 09:04

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

15 Answers15

Some Tips

1. Multiple DB connections

2. Where to enable query log ?

3. Memory

Linked