Laravel 4 - Logging SQL Queries

Laravel 4 - logging SQL queries

Here is what I am currently using for logging of sql queries. You should be able to drop this into your main routes file then add 'log' => true into your database config.

if (Config::get('database.log', false))
{           
    Event::listen('illuminate.query', function($query, $bindings, $time, $name)
    {
        $data = compact('bindings', 'time', 'name');

        // Format binding data for sql insertion
        foreach ($bindings as $i => $binding)
        {   
            if ($binding instanceof \DateTime)
            {   
                $bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
            }
            else if (is_string($binding))
            {   
                $bindings[$i] = "'$binding'";
            }   
        }       

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

        Log::info($query, $data);
    });
}

Thanks to Jeemusu answer for the bit about inserting the bindings into the prepared statement.

Laravel 4 : How to display SQL queries ran?

A popular method is to monitor the Event for Eloquent, and output any queries run on the database as you are going along:

Event::listen('illuminate.query', function($query, $params, $time, $conn) 
{ 
    dd(array($query, $params, $time, $conn));
});

$allMessages = Messages::with('User')->whereIn('conv_id',$conv_id)->orderBy('created_at','aadesc')->take(10);

That will output the query that is run.

Another option is to use a Laravel4 Debugger package, which automatically shows you the queries run: https://github.com/barryvdh/laravel-debugbar

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

Can laravel log sql queries after all?

I would use middleware to enable it. This would allow you to enable with options (like only in local environments, etc).

You could gather the information with dd() or using Log::info(), etc. Something like:

namespace App\Http\Middleware;

use DB;
use Log;
use Closure;

class EnableQueryLogMiddleware
{
    public function handle($request, Closure $next)
    {
        if (env('local')) {
            DB::enableQueryLog();
        }

        return $next($request);
    }

    public function terminate($request, $response)
    {
        // Here you can either Log it, DD, etc.
        dd(DB::getQueryLog());
        Log::info(DB::getQueryLog());
    }
}

Get executed sql queries in Laravel

The problem is there's a difference in time of execution between the code executed inside the ::listen() method and the lines before and after.

The code inside will be executed when a query is sent, while the other code is performed on startup of your app. Thus, after binding the function to the Database facade, no query is executed yet and $logs will be empty.

For logging

You could try and let the DB listener write the queries to a file or have a look at this post: https://stackoverflow.com/a/27753889/2142071

For adding to request

Inside the listen method use request()->request->add(['sql', $query->sql]); to have the sql variable available in the request object.

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

---

Related Topics