DbQueryPhp5.php

<?php

use Carbon\Carbon;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\QueryException;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Database\Query\Expression;
use Illuminate\Contracts\Support\Jsonable;
use Illuminate\Contracts\Support\Arrayable;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;

/**
 * Class DbQuery
 * @package App\Helpers
 */
class DbQuery implements Arrayable, Jsonable
{
    /**
     * @var bool
     */
    protected static $recording = false;

    /**
     * @var array
     */
    protected static $queries = [];

    /**
     * @var bool
     */
    protected static $trace = false;

    /**
     * @var bool
     */
    public static $format = true;

    /**
     * @param bool $trace
     * @return $this
     */
    public function setTrace($trace = false)
    {
        self::$trace = $trace;

        return $this;
    }

    public function __construct()
    {
        $this->replaceArrayMacro();
        static::$format = class_exists('SqlFormatter') ? static::$format : false;
    }

    /**
     * @param object $query
     * @return void
     */
    public function handle($query)
    {
        if (self::$recording) {
            $connection = $query->connection;
            $config = app('config')['database.connections'][$connection->getName()];
            $database = $query->connection->getDatabaseName();
            $time = round($query->time / 1000, 2);
            $sql = $this->compileQuery($query);

            try {
                $sql = static::$format ? \SqlFormatter::format($sql, false) : $sql;
            } catch (Exception $exception) {
                //
            }

            //Not all configs will exist depending on the source selection
            $driver = isset($config['driver']) ? $config['driver'] . ':' : '';
            $username = isset($config['username']) ? $config['username'] . '@' : '';
            $host = isset($config['host']) ? $config['host'] : '';
            $port = isset($config['port']) ? ':' . $config['port'] : '';
            $source = $driver.$username.$host.$port;

            $args = compact('source', 'database', 'sql', 'time');

            if (self::$trace) {
                $args = app(DebugTrace::class)->generate($args)->truncate();
            }

            self::$queries[] = $args;
        }
    }

    /**
     * @return array
     */
    public function dump()
    {
        $queries = self::$queries;
        self::$queries = [];

        return $queries;
    }

    /**
     * @return $this
     */
    public function record()
    {
        self::$recording = true;
        self::$queries = [];

        return $this;
    }

    /**
     * @return array
     */
    public function toArray()
    {
        return self::$queries;
    }

    /**
     * @param int $options
     * @return false|string
     */
    public function toJson($options = 0)
    {
        $traces = $this->toArray();
        $options = $options ?: JSON_PRETTY_PRINT;

        return json_encode($traces, $options);
    }

    /**
     * @return false|string
     */
    public function __toString()
    {
        return print_r($this->toArray(), true);
    }

    /**
     * @param string|EloquentBuilder|QueryBuilder|QueryException|Expression $sql
     * @param array $values
     * @param PDO|string|null $pdo
     * @return string
     */
    public function compileQuery($sql, array $values = [], $pdo = null)
    {
        if (is_object($sql)) {
            if ($sql instanceof EloquentBuilder) {
                $clauses = array_map(function (JoinClause $join) {
                    return $join->bindings;
                }, (array)$sql->getQuery()->joins);
                
                $values = $clauses ? array_merge(array_merge(...$clauses), $sql->getQuery()->getBindings())
                    : $sql->getQuery()->getBindings();
                $sql = $sql->getQuery()->toSql();
            } elseif ($sql instanceof QueryBuilder) {
                $clauses = array_map(function (JoinClause $join) {
                    return $join->bindings;
                }, (array)$sql->joins);
                
                $values = $sql->getBindings() ? array_merge(array_merge(...$clauses), $sql->getBindings()) : [];
                $sql = $sql->toSql();
            } elseif ($sql instanceof QueryException) {
                $values = $sql->getBindings();
                $sql = $sql->getSql();
            } elseif ($sql instanceof stdClass) {
                $pdo = $sql->connection->getPdo();
                $values = $sql->bindings;
                $sql = $sql->sql;
            }
        }

        $enclosures = [
            'back_tick' => '`',
            'apostrophe' => "'"
        ];

        $matches = [];
        foreach ($enclosures as $name => $enclosure) {
            $matches[$name] = [];
            preg_match_all("/$enclosure.*?$enclosure/", $sql, $matches[$name]);
            $matches[$name] = last($matches[$name]);
            $sql = preg_replace("/$enclosure.*?$enclosure/", "$enclosure?$enclosure", $sql);
        }

        $sql = strpos($sql, $enclosures['back_tick']) ? strtoupper($sql) : $sql;

        foreach ($enclosures as $name => $enclosure) {
            $sql = Str::replaceArray("$enclosure?$enclosure", $matches[$name], $sql);
        }

        $values = array_map(function ($value) use ($pdo) {
            if (!is_numeric($value) && !is_null($value)) {
                /** @var PDO|Carbon|DateTime $value */
                $value = $value instanceof Carbon ? $value->toDateTimeString() : $value;
                $value = $value instanceof DateTime ? $value->format('Y-m-d H:i:s') : $value;
//                $value = ($pdo instanceof PDO ? $pdo : DB::connection($pdo)->getPdo())->quote($value);
            }

            return $value;
        }, $values);

        $sql = str_replace(' AND 1', '', $sql);
        $sql = str_replace('WHERE 1 AND ', 'WHERE ', $sql);

//        $sql = Str::replaceArray('?', $values, $sql);
        $sql = preg_replace_callback('~(.?)(\?)(.?)~', function ($match) use (&$values) {
            $value = array_shift($values);
            $needsEncapsulation = !(is_numeric($match[0]) || in_array($match[0], ['`?`', '\'?\'']));

            return $match[1] . ($needsEncapsulation ? "'$value'" : $value) . $match[3] ;
        }, $sql);

        $sql = preg_replace('/\s+/', ' ', $sql);
//        $sql = preg_replace('/(` AS `)(.+?)(`)/', "$1'$2'", $sql);
//        $sql = str_replace('`', '', $sql);
//        $sql = preg_replace("/( AS )(')(.+?)(')/", "$1`$3`", $sql);
        $sql = rtrim($sql, ';').';';
        #$sql .= '# '.$query['time'].'ms';

        return $sql;
    }

    protected function replaceArrayMacro()
    {
        if (!method_exists(Str::class, 'replaceArray')
            && !Str::hasMacro('replaceArray')
        ) {
            Str::macro('replaceArray', function ($search, array $values, $subject) {
                return preg_replace_callback('~' . preg_quote($search) . '~', function($match) use (&$values) {
                    return array_shift( $values);
                }, $subject);
            });
        }
    }

    public static function listen()
    {
        DB::listen(function ($query, array $bindings, $time, $name) {
            app(static::class)->handle((object)[
                'connection' => DB::connection($name),
                'bindings' => $bindings,
                'sql' => $query,
                'time' => $time
            ]);
        });
    }
}

debug.php

<?php

if (!function_exists('query_log')) {
    /**
     * (De)activate monitoring of SQL query executions and return all that were run while active
     *
     * @param bool $trace Indicate whether you want a stack-trace pointing to where the query came from
     * @return DbQuery|string
     */
    function query_log($trace = false)
    {
        static $ready = false;
        $ready = !$ready;

        $queryLog = app(DbQuery::class)->setTrace($trace);
        $ready ? $queryLog::listen() : app('events')->forget('illuminate.query');

        return $ready ? $queryLog->record() : $queryLog->setTrace()->dump();
    }
}