Laravel Eloquent Display Query Log

Laravel Eloquent display query log

First you have to enable query log
it can be done using

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

then you can use below code to see the query log

$queries = DB::getQueryLog();

if you want to see the last executed query

$last_query = end($queries);

to know more about logging see this https://laravel.com/docs/5.0/database#query-logging

Example

public function show(Order $order){
    \DB::connection()->enableQueryLog();
    $data = $order->all();
    $queries = \DB::getQueryLog();
    return dd($queries);
}

How do I get the query builder to output its raw SQL query as a string?

To output to the screen the last queries ran you can use this:

\DB::enableQueryLog(); // Enable query log

// Your Eloquent query executed by using get()

dd(\DB::getQueryLog()); // Show results of log

I believe the most recent queries will be at the bottom of the array.

You will have something like that:

array(1) {
  [0]=>
  array(3) {
    ["query"]=>
    string(21) "select * from "users""
    ["bindings"]=>
    array(0) {
    }
    ["time"]=>
    string(4) "0.92"
  }
}

(Thanks to Joshua's comment below.)

Laravel Eloquent builder query log

I have been digging deeper within Laravel itself, and the answer to the question as why doesn't the toSql() method dump the relation SQL as well is because it gets compiled before eagerLoadRelations method is triggered.

So currently, it is not possible to dump the whole SQL while operating with query builders.

What halloei suggests will dump all queries executed, however this does not help me at this point because it requires me to add the code outside my package.

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

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

How to log every query from multiple connections in Eloquent (outside laravel)

To log queries of multiple db connections in sequential order, we'll need to enableQueryLog first, have separate Logger configured.

$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);

$capsule->connection('<MySqlConnectionName>')->enableQueryLog();
$capsule->connection('<SqlServerConnectionName>')->enableQueryLog();
//$capsule->connection('<MongoConnectionName>')->enableQueryLog(); 

// Listen
\Illuminate\Database\Capsule\Manager::listen(function($query) {
    if($query->connectionName == 'mysql') {
        $mysqlLogger->debug('mysql', [
            'query' => $query->sql,
            'bindings' => $query->bindings
        ]);
    } elseif($query->connectionName == 'sqlserver') {
        $sqlServerLogger->debug('mongodb', [
            'query' => $query->sql,
            'bindings' => $query->bindings
        ]);
    } /*elseif($query->connectionName == 'mongodb') {
        $mongoDbLogger->debug('mongodb', [
            'query' => $query->sql,
            'bindings' => $query->bindings
        ]);
    }*/
});

How to log all Eloquent queries

Listen for query events on the connection:

$capsule = new \Illuminate\Database\Capsule\Manager;
$capsule->addConnection($config['db']);
$capsule->getConnection("default")->enableQueryLog();
$capsule->setAsGlobal();

$capsule->getConnection()->setEventDispatcher(new \Illuminate\Events\Dispatcher);
$capsule->getConnection()->listen(function ($query) {
    // TODO: Log query.
});

$capsule->bootEloquent();

Laravel - how to see source of queries?

As you can see in Connection::logQuery(), each database query triggers a QueryExecuted event.

You could listen to this event, and use debug_backtrace() to crawl up the function calls.

use Illuminate\Support\Facades\DB;

DB::listen(function ($query) {

    // send this to a log or whatever
    $backtrace = debug_backtrace();

    // also, you can read $query to get the SQL string, bindings...
});

The main work will probably be to filter the data, to isolate what you are looking for.

Laravel Lumen - Eloquent Query Log

To get the query log in Laravel Lumen working you need to enable it:

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

You can add that code in to your controller, middleware, etc then use:

$queries    = DB::getQueryLog();
$lastQuery = end($queries);

dd($lastQuery)

To print your query.

You can also use the following with eloquent:

$myModel = Users::where('active', true);

dd($myModel->getSql(), $myModel->getBindings());

You must run the getSql() and getBindings() before you call ->first() or ->get(), etc

---

Related Topics