Laravel ORM 很好用讓我們不用寫 SQL，但常常會需要看自己的 SQL 有無效能問題跟符合預期，會在 Builder 使用 toSql() 或 dd()，Laravel 10 後的版本還有新增直接幫你把 bindings 帶入的 toRawSql() 和 ddRawSql() 讓開發者更方便觀看 ORM 的 SQL。

toSql()

User::query()->where('email', 'MTsung103035@gmail.com')->toSql();
// select * from `users` where `email` = ?

dd()

User::query()->where('email', 'MTsung103035@gmail.com')->dd();
// select * from `users` where `email` = ?
// ['MTsung103035@gmail.com']

toRawSql()

User::query()->where('email', 'MTsung103035@gmail.com')->toRawSql();
// select * from `users` where `email` = 'MTsung103035@gmail.com'

ddRawSql()

User::query()->where('email', 'MTsung103035@gmail.com')->ddRawSql();
// select * from `users` where `email` = 'MTsung103035@gmail.com'

但每次都要一個一個 toRawSql() 實在有點不方便，所以 Laravel 有提供 DB::listen 用來聆聽所有 DB 操作的 function

use Illuminate\Support\Facades\DB;

DB::listen(function ($query) {
    // $query->sql;
    // $query->bindings;
    // $query->time;
    // $query->toRawSql();
});

有了這個方法後就能加到 AppServiceProvider 讓每個 request 的 SQL 都紀錄起來，記得設定 env QUERY_LOG 只在開發環境中紀錄。

<?php
 
namespace App\Providers;
 
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\ServiceProvider;
 
class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     */
    public function register(): void
    {
        // ...
    }
 
    /**
     * Bootstrap any application services.
     */
    public function boot(): void
    {
        if (env('QUERY_LOG', false)) {
            DB::listen(function ($query) {
                Log::channel('sql-query')->debug('', [
                    $query->toRawSql(),
                    $query->time . 'ms',
                ]);
            });
        }
    }
}

Log

[2025-03-28 08:07:07] develop.DEBUG:  ["select count(*) as aggregate from `member`","384.33ms"]
[2025-03-28 08:07:07] develop.DEBUG:  ["select * from `member` order by `id` desc limit 5 offset 0","81.03ms"]
[2025-03-28 08:07:07] develop.DEBUG:  ["select * from `member_creditcard` where `member_creditcard`.`member_id` in (1, 2, 3, 4, 5) and `member_creditcard`.`deleted_at` is null order by `is_default` desc, `id` desc","81.01ms"]
[2025-03-28 08:07:07] develop.DEBUG:  ["select `laravel_table`.*, @laravel_row := if(@laravel_group = `member_id`, @laravel_row + 1, 1) as `laravel_row`, @laravel_group := `member_id` from (select @laravel_row := 0, @laravel_group := 0) as `laravel_vars`, (select * from `task` where `task`.`member_id` in (1, 2, 3, 4, 5) order by `task`.`member_id` asc, `id` desc) as `laravel_table` having `laravel_row` <= 1 order by `laravel_row`","83.59ms"]