QueryLog.php

<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Http\Request;
use Symfony\Component\Console\Helper\Table;
use Symfony\Component\Console\Helper\TableCell;
use Symfony\Component\Console\Output\StreamOutput;

/**
 * Class QueryLog
 *
 * @package App\Http\Middleware
 * @author  Guilherme Pressutto <gpressutto5@gmail.com>
 */
class QueryLog
{
    /**
     * @var StreamOutput
     */
    private $output;

    /**
     * @var array
     */
    private $queryRows;

    /**
     * @param Request $request
     * @param Closure $next
     *
     * @return mixed
     * @throws \Exception
     */
    public function handle(Request $request, Closure $next)
    {
        if (!env('DB_LOG', false)) {
            return $next($request);
        }

        $logFile = $this->getLogFileResource();

        $this->output = new StreamOutput($logFile);

        $this->makeRouteInfoTable($request);
        $this->makeRequestContentTable($request);

        \DB::listen(function (QueryExecuted $query) {
            $this->logQuery($query);
        });

        $microtimeBefore = microtime(true);
        $response = $next($request);
        $microtimeAfter = microtime(true);

        $this->makeQueryInfoTable();

        $executionTime = $this->formatDuration(($microtimeAfter - $microtimeBefore) * 1000);
        $queryNumber = count($this->queryRows);
        $this->output->writeln("Made $queryNumber queries in $executionTime");

        fclose($logFile);

        return $response;
    }

    /**
     * @return resource
     * @throws \Exception
     */
    private function getLogFileResource()
    {
        $databaseLogDir = storage_path('logs' . DIRECTORY_SEPARATOR . 'database');
        if (!is_dir($databaseLogDir)) {
            mkdir($databaseLogDir);
        }

        $tempFileName = tempnam($databaseLogDir, date('Y-m-d_H:i:s') . '_query_');
        $fileName = $tempFileName .'.log';
        rename($tempFileName, $fileName);
        chmod($fileName, 0644);
        $logFile = fopen(
            $fileName,
            'a+'
        );

        if (!$logFile) {
            throw new \Exception('Unable to open log file stream.');
        }

        return $logFile;
    }

    private function createTableWithHeaders(array $headers): Table
    {
        return (new Table($this->output))->setHeaders($headers);
    }

    private function getActionShortNameForRequest(Request $request): string
    {
        $route = \Route::getRoutes()->match($request);

        return str_replace('App\\Http\\Controllers\\', '', $route->getActionName());
    }

    private function makeRouteInfoTable(Request $request): void
    {
        $routeInfoTable = $this->createTableWithHeaders([
            [
                new TableCell('Route Info', ['colspan' => 4]),
            ],
            [
                'Time',
                'Method',
                'URI',
                'Action',
            ],
        ]);

        $routeInfoTable->addRow([
            \Carbon::now(),
            $request->getMethod(),
            $request->getRequestUri(),
            $this->getActionShortNameForRequest($request),
        ]);

        $routeInfoTable->render();
    }

    private function makeRequestContentTable(Request $request): void
    {
        if (!$request->all()) {
            return;
        }

        $requestContentTable = $this->createTableWithHeaders(['Request Content']);
        $requestContentTable->addRow([print_r($request->all(), true)]);
        $requestContentTable->render();
    }

    private function makeQueryInfoTable(): void
    {
        $queryInfoTable = $this->createTableWithHeaders([
            [
                new TableCell('Query Info', ['colspan' => 3]),
            ],
            [
                'Time',
                'Location',
                'Query',
            ],
        ]);

        $queryInfoTable->addRows($this->queryRows);
        $queryInfoTable->render();
    }

    private function logQuery(QueryExecuted $query): void
    {
        $controllerCall         = $this->getControllerCall();
        $controllerRelativePath = str_replace(base_path() . '/', '', $controllerCall['file']);
        $callLocation           = $controllerRelativePath . ':' . $controllerCall['line'];

        $this->queryRows[] = [
            $this->formatDuration($query->time),
            $callLocation,
            $this->getBoundSqlForQuery($query),
        ];
    }

    private function formatDuration(float $miliseconds): string
    {
        if ($miliseconds < 1) {
            return round($miliseconds * 1000) . 'μs';
        }

        if ($miliseconds > 1000) {
            return round($miliseconds / 1000) . 's';
        }

        return round($miliseconds, 2) . 'ms';
    }

    private function getBoundSqlForQuery(QueryExecuted $query): string
    {
        $preparedSql = str_replace(['%', '?'], ['%%', '%s'], $query->sql);

        return vsprintf($preparedSql, $query->bindings);
    }

    private function getControllerCall(): array
    {
        $trace         = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS | DEBUG_BACKTRACE_PROVIDE_OBJECT, 50);
        $filteredTrace = array_filter($trace, function ($item) {
            return isset($item['file'])
                   && strpos($item['file'], base_path() . '/app/Http/Controllers/') !== false;
        });

        if (!$filteredTrace) {
            $filteredTrace = array_filter($trace, function ($item) {
                return isset($item['file'])
                       && strpos($item['file'], base_path() . '/app/') !== false
                       && $item['file'] !== __FILE__;
            });
        }

        if (!$filteredTrace) {
            $filteredTrace = $trace;
        }

        return array_first($filteredTrace);
    }
}