Laravel 5.3 - How to log all queries on a page?

Question

My team and I are working on a rather big project. There's queries going on everywhere - in controllers, in view composers in views (lazy loading) and probably in some other services as well. It's getting hard to keep a track of it all and the page load speed is fairly slow at the moment.

Where would I put \DB::enableQueryLog() and \DB::getQueryLog() to log ALL the queries and dump them? Basically I'm looking for some place in code that happens before any of the queries happen (to put enableQueryLog()) and I'm looking for a place that happens after the views render (to dump getQueryLog()).

What would be a good way to go about this?

Thanks in advance.

may be you can sore the query in a separate table using getQueryLog() method — kapilpatwa93, Dec 15 '16 at 11:46
Possible duplicate of [How to get the query executed in Laravel 5 ? DB::getQueryLog returning empty array](http://stackoverflow.com/questions/27753868/how-to-get-the-query-executed-in-laravel-5-dbgetquerylog-returning-empty-arr) — Overdrivr, Dec 15 '16 at 13:17

score 65 · Accepted Answer · edited Jan 10 '17 at 23:02

65

Here comes the perfect example:

https://laravel.com/docs/5.3/database#listening-for-query-events

Open app\Providers\AppServiceProvider.php and add the following to Boot() function:

DB::listen(function ($query) {
    var_dump([
        $query->sql,
        $query->bindings,
        $query->time
    ]);
});

edited Jan 10 '17 at 23:02

Justin

26,443
16
111
128

answered Dec 15 '16 at 11:47

Hudson Pereira

1,066
8
13

From my understaning this starts listening to the queries, but where would I dump them? I see the other answer linking to terminable middleware. So a good way would be to implement the terminate() method in a middleware and dump it there? – DevK Dec 15 '16 at 12:22
2

You can just Log::info($query->sql) – Hudson Pereira Dec 15 '16 at 12:23
2

So much yes. Easy to implement when needed. Using `\Log::debug('query', [...])` I was able to log everything into the laravel log and find inefficient queries being executed where I could eager load instead. Thank you. – Stoutie Jul 11 '17 at 04:36
It does not show where db transaction is started and committed. – Yevgeniy Afanasyev Dec 31 '18 at 03:44

score 19 · Answer 2 · answered Mar 31 '17 at 09:48

You can add this to the Providers/AppServiceProvider.php file and check them in the laravel log file with tail:

tail -f storage/logs/laravel.log

You can even filter with queries you want to log. For example, here I was using Laravel Passport, and didn't want to log all the oauth queries.

use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\Event;
use Illuminate\Support\Facades\Log;

public function register() {
    if (App::environment('local') && env('APP_URL') == 'http://localhost') {
        Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
            // filter oauth ones
            if (!str_contains($query->sql, 'oauth')) {
                Log::debug($query->sql . ' - ' . serialize($query->bindings));
            }
        });
    }
}

Thanks for `Event::listen`, I got errors in test environment with `DB::listen` — James Bond, Dec 13 '19 at 09:06

score 3 · Answer 3 · edited Aug 14 '19 at 11:37

Put this code right above the code where your query is executed

\DB::listen(function($sql) {
  die(\Illuminate\Support\Str::replaceArray('?', $sql->bindings, $sql->sql));
});

Just modified for executable query:

\DB::listen(function ($query) {
   // Enclose in single quotes for string params.
   $bindings = collect($query->bindings)->map(function ($param) {
      if(is_numeric($param)) {
        return $param;
      } else {
        return "'$param'";
      }
   });

   \Log::info(\Illuminate\Support\Str::replaceArray('?', $bindings->toArray(), $query->sql));
});

score 2 · Answer 4 · edited Sep 11 '18 at 13:26

If you want to print a query which is executed on your app do following steps.

Step1: Go to your AppServiceProvider.php file. // File path App\Providers\AppServiceProvider.php

Step2: Make boot() method and paste below code.

public function boot() {
        // Log queries
        if (true) {
            \DB::listen(function ($query) {
                \Log::info(
                    $query->sql, $query->bindings, $query->time
                );
            });
        }
    }

Step3: Now you can see you queries in lumen.log or laravel.log file. File path is laravel_app\storage\logs\laravel.log or lumen.log.

Enjoy....

score 1 · Answer 5 · answered Dec 15 '16 at 11:41

1

add a middleware that executes after the request is done and logs your queries ... see Terminable Middlwares

answered Dec 15 '16 at 11:41

Sherif

1,477
1
14
21

By that time the queries would have finished since terminable middleware gets called when request is done. – Mjh Dec 15 '16 at 11:43

score 0 · Answer 6 · edited May 23 '17 at 10:30

0

Are you using MySQL? You can just tail the log.

How to show the last queries executed on MySQL?

Or use the Laravel Debug Bar?

edited May 23 '17 at 10:30

Community

1
1

answered Dec 15 '16 at 14:19

Mick

1,401
4
23
40

score 0 · Answer 7 · edited Jun 20 '20 at 09:12

0

Additioanlly There's package available also:

log-my-queries

https://packagist.org/packages/technoknol/log-my-queries

Just install and add it's entry to middleware. It will log all the queries in laravel.log default log file.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 13 '18 at 13:53

shyammakwana.me

5,562
2
29
50

Laravel 5.3 - How to log all queries on a page?

7 Answers7

log-my-queries

Linked