tohokuaikiのチラシの裏

技術的ネタとか。

LaravelでSQLログを出力するのをProviderで実装する

以前書いたコレ。

tohokuaiki.hateblo.jp

Providerで実装する。

Providerを作る

こちらを参考に。 qiita.com

app/Providers/DataBaseQueryServiceProvider.php

<?php
declare(strict_types=1);

namespace App\Providers;

use Carbon\Carbon;
use DateTime;
use DB;
use Event;
use Illuminate\Database\Events\TransactionBeginning;
use Illuminate\Database\Events\TransactionCommitted;
use Illuminate\Database\Events\TransactionRolledBack;
use Illuminate\Support\ServiceProvider;
use Log;

class DataBaseQueryServiceProvider extends ServiceProvider
{
    /**
     * Register services.
     *
     * @return void
     */
    public function register(): void
    {
        if (env('APP_SQL_DEBUG') !== true) {
            return;
        }

        DB::listen(function ($query) {
            $sql = $query->sql;
            foreach ($query->bindings as $binding) {
                if (is_string($binding)) {
                    $binding = "'{$binding}'";
                } elseif (is_bool($binding)) {
                    $binding = $binding ? '1' : '0';
                } elseif ($binding === null) {
                    $binding = 'NULL';
                } elseif ($binding instanceof Carbon) {
                    $binding = "'{$binding->toDateTimeString()}'";
                } elseif ($binding instanceof DateTime) {
                    $binding = "'{$binding->format('Y-m-d H:i:s')}'";
                }

                $sql = preg_replace("/\?/", $binding, $sql, 1);
            }

            Log::channel('sqllog')->debug('SQL', ['sql' => $sql, 'time' => "$query->time ms"]);
        });

        Event::listen(TransactionBeginning::class, function (TransactionBeginning $event) {
            Log::channel('sqllog')->debug('START TRANSACTION');
        });

        Event::listen(TransactionCommitted::class, function (TransactionCommitted $event) {
            Log::channel('sqllog')->debug('COMMIT');
        });

        Event::listen(TransactionRolledBack::class, function (TransactionRolledBack $event) {
            Log::channel('sqllog')->debug('ROLLBACK');
        });
    }
}

Providerを登録する

config/app.php

<?php
    'providers' => [
.../*
         * Package Service Providers...
         */

        /*
         * Application Service Providers...
         */
        App\Providers\DataBaseQueryServiceProvider::class,

loggerを登録する

config/logging.php

<?php
        'emergency' => [
            'path' => storage_path('logs/laravel.log'),
        ],

        'sqllog' => [
            'driver' => 'daily',
            'path' => storage_path('logs/sql.log'),
            'level' => 'debug',
        ],