Laravel 5: How to reset SQL query?

Question

Laravel 5 Embedded Solution

In Laravel 5+, we can use \DB::getQueryLog()to retrieve all executed queries. Since query logging is an extensive operation and causes performance problems, it is therefore disabled by default in L5 and is recommended only for the development environment. We can enable query logging using the method \DB::enableQueryLog()as described in [Laravel documentation] [1].

The problem is the built-in solution

The function DB::getQueryLog()is great, but sometimes we want it to be great if we get a dump in a flat SQL format, so we can copy / skip it in our favorite MySQL application, for example, phpMyAdminor Sqlyogto execute it and debug or optimize.

So, I need a helper function that helps me dump with the following additional information:

• On which file and line number is the dump called.
• Remove back ticks from request.
• A flat query, so you don’t need to update the binding parameters manually, and I can copy / past SQLto phpMyAdminetc. to debug / optimize the request.

Answer 1

Custom solution

Step 1: Enable Query Logging

Copy / past the following code on top of the route file:

# File: app/Http/routes.php
if (\App::environment( 'local' )) { 
   \DB::enableQueryLog();
}

Step 2: add a helper function

if (!function_exists( 'dump_query' )) {
function dump_query( $last_query_only=true, $remove_back_ticks=true ) {

    // location and line
    $caller = debug_backtrace( DEBUG_BACKTRACE_IGNORE_ARGS, 1 );
    $info = count( $caller ) ? sprintf( "%s (%d)", $caller[0]['file'], $caller[0]['line'] ) : "*** Unable to parse location info. ***";

    // log of executed queries
    $logs = DB::getQueryLog();
    if ( empty($logs) || !is_array($logs) ) {
        $logs = "No SQL query found. *** Make sure you have enabled DB::enableQueryLog() ***";
    } else {
        $logs = $last_query_only ? array_pop($logs) : $logs;
    }

    // flatten bindings
    if (isset( $logs['query'] ) ) {
        $logs['query'] = $remove_back_ticks ? preg_replace( "/`/", "", $logs['query'] ) : $logs['query'];

        // updating bindings
        $bindings = $logs['bindings'];
        if ( !empty($bindings) ) {
            $logs['query'] = preg_replace_callback('/\?/', function ( $match ) use (&$bindings) {
                return "'". array_shift($bindings) . "'";
            }, $logs['query']);
        }
    }
    else foreach($logs as &$log) {
        $log['query'] = $remove_back_ticks ? preg_replace( "/`/", "", $log['query'] ) : $log['query'];

        // updating bindings
        $bindings = $log['bindings'];
        if (!empty( $bindings )) {
            $log['query'] = preg_replace_callback(
                '/\?/', function ( $match ) use ( &$bindings ) {
                return "'" . array_shift( $bindings ) . "'";
            }, $log['query']
            );
        }
    }

    // output
    $output = ["*FILE*" => $info,
               '*SQL*' => $logs
    ];

    dump( $output );
}

}

How to use?

Dump the last executed request, use immediately after the request:

dump_query();

:

dump_query( false );

Answer 2

• .

, , , , , .

• back-ticks .

back-ticks, MySQL .

• , , / SQL phpMyAdmin .., / .

vsprintf :

$queries = DB::getQueryLog();

foreach ($queries as $key => $query) {
    $queries[$key]['query'] = vsprintf(str_replace('?', '\'%s\'', $query['query']), $query['bindings']);
}

return $queries;

github repo squareboat/sql-doctor

Answer 3

. Laravel 5.2 rout.php Laravel 5. 3+ web.php

<?php
// Display all SQL executed in Eloquent

Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
    var_dump($query->sql);
    var_dump($query->bindings);
    var_dump($query->time);
    echo "<br><br><br>";
});