【Laravel】unionで取得したレコード同士を足し合わせる

こんにちは、おーしまです。

今日は、久しぶりにLaravelのクエリビルダの投稿です。

取得したレコード同士を足し合わせるunionの紹介です。
私は、unionを知らなかったので、めちゃくちゃハマりました。

例えば、
usersテーブル
testsテーブル
あったとします。

userはテストを行い、テストの点をuser_idで紐付けて、testsテーブルで管理します。

取得したいtestsテーブルのレコード条件は、

・各ユーザーのテストの中での最小点のレコード
・ただし、赤点（50点以下）のテストがある場合は、赤点テストの全レコード

です。
こちらを合わせて、点数の昇順にして返したいです。

つまり、
f:id:tomo_bb_aki0118115:20210904191646p:plain
つまり、上画像で言うと、ピンクのレコードを取得したいです。

これを取得するには、
１、各ユーザーのテストの中で50点以上かつ、最小点のレコードを取り出す。
２、１で取得したものから、50点以下のテストがある場合は、user_idを特定してwhereNotInで含めない。
３、joinして全カラムを取得
４、50点以下のレコードを取り出す。
５、３と４で取り出したレコードを足し合わせる。

この手順でいけると思います。

コード

<?php

    //最低点の絞り込み条件（結合用）
    $minScore = Test::select('user_id', DB::raw('min(score) as score'))
    //50点以上の中で、、
    ->where('score', '>', 50)
    //ユーザーに50点以下のテストがある人は除く
    ->whereNotIn('user_id', function ($query) use ($condition){
      $query
      ->select('user_id')
      ->from('tests')
      ->where('score', '<', 50);
    })
    //user_idでグループ化することで、そのuserの最初点テストを取得する
    ->groupBy('user_id');
     
    //最低点テストを取得
    $tests = Test::select('t.*')->from('tests as t')
    ->joinSub($minScore, 'ms', function ($join) {
      $join->on('t.user_id', '=', 'ms.user_id')
      ->on('t.score', '=', 'ms.score');
    });
     
    //赤点のテストを取得
    $redScore = Test::where('score', '<', 50);
     
    //赤点と最低点を合わせる
    $tests
    ->union($redScore)
    //テストの点で昇順に並び替え
    ->orderBy('score')
    ->get();

    return $tests;