酷すぎる自分のSQL（Laravel query builder）を掃除した話

Laravelのクエリビルダを使ってデータを取得する際のアプローチが力技もいいところでしたので、修正していった過程を投稿します。

Laravel version 8.83.26
MySQL version 8.0.31

やりたいこと

下記のロジックを成立させるクエリを作成したい

• ユーザーがログイン時にPOPUPを表示
• POPUPは管理画面から登録可能
• ユーザーはPOPUPを見た時「次回から非表示」を選択できる
• 「次回から非表示」をしたPOPUPは除外して表示

元々の実装案

３つのテーブルをJOINさせて表示させようとしていた。

• POPUP内容を登録するテーブル（notifications）
• ユーザーテーブル(users)
• 「次回非表示」を選択したユーザを登録する中間テーブル（notificatoin_user）
  ※中間テーブルはLaravelの命名規則に従って命名

最初の構文（ひどい）

//notificationController内

DB::table('users')
            ->leftJoin('notification_user', 'notification_user.user_id', "=", 'users.id')
            ->rightJoin('notifications', 'notifications.id', "=", 'notification_user.notification_id')
            ->where('users.id', "=", null)
            ->orWhereNotIn('users.id', [$auth_user->id])
						->get();

やっていることは、一言で言うと、
「全部取得しちゃえ！」
です。
エンジニアになる前から、上記のようなクエリを作成する機会がなかったので、ここでしっかり整理しておこうと思い執筆しました。
上記の記述は全てを取得している状況なので、ここから無駄な肉を削ぎ落として行きます。

修正した構文が以下の通り。

DB::table('notifications as n')
        ->select( 'n.id as id',
                  'n.image as image',
                  'n.already_read as already_read', 
                  'n.hide_next_time as hide_next_time', 
                  'n.jump_link as jump_link', 
                  'n.notify_priority as notify_priority', 
                  'nu.read as read',
                  'nu.hide_next as hide_next' )
        ->leftJoin('notification_user as nu', function ($join) {
            $join->on('n.id', "=", 'nu.notification_id')
                 ->where('nu.user_id', "=", auth()->user()->id);
        })
        ->where('nu.user_id', null)
        ->orderBy('n.notify_priority')
        ->orderBy('n.id')
        ->get();

ちょうどこちら【SQL】今日からできるクエリチューニングにSQLのパフォーマンス向上に関する記事もあったので、実際に活用してみました！

主な修正点

• そもそもusers テーブルをJOINする意味が全くないので削除
• $join->on を追加して、JOINに制約を追加
• where の前にリクエストユーザーのIDと中間テーブルのユーザーIDの一致データを抽出する方式に変更
  →これによって、JOINの段階でuser_id カラムには自分のID以外のレコードをnull にすることができる。
• エイリアスを設定（クエリの処理速度が上がるらしい）

これで少しは綺麗に記述できている、はず。。
間違っていたり、もっといい書き方があれば押して得ていただけると嬉しいです！