📘
グループ毎に最新のレコードを取得する Laravel SQL
はじめに
最新日付のレコードを取得、表示させる方法について記述します。環境php7.4、laravel6
テーブル
テーブルのサンプルです
studentテーブル
| id | name |
|---|---|
| 1 | 吉田 |
| 2 | 田中 |
| 3 | 佐藤 |
| 4 | 加藤 |
scoreテーブル
| id | student_id | score | date |
|---|---|---|---|
| 1 | 1 | 60 | 2021-9-20 |
| 2 | 2 | 80 | 2021-10-11 |
| 3 | 4 | 70 | 2021-10-13 |
| 4 | 1 | 80 | 2021-7-20 |
| 5 | 4 | 80 | 2021-8-10 |
| 6 | 3 | 40 | 2021-11-16 |
| 7 | 1 | 90 | 2021-7-15 |
SQLで記述
group byとinner joinで相関問い合わせにより生徒毎に最新の１件を取得
select *
from scores as T1
inner join (select student_id, MAX(date) as latest from scores group by student_id) as T2
ON T1.student_id ＝ T2.student_id
where T1.date = T2.latest
考え方
select 取得したい列
from テーブル 別名A
inner join (select グループ列, max(最大値を抽出する列) 別名 from テーブル group by グループ列) 別名 B
on 別名A.グループ列 ＝ 別名B.グループ列
where 別名A.最大値を求める列 = 別名B.最大値
Laravelで記述
studentからリレーション
public function scores()
{
return $this->hasMany(Score::class);
}
sqlで書くのと考え方は同じ、joinsubを使いjoinさせる。
scores.dateのようにテーブル名を含めたカラム指定をエスケープさせずに渡すためにrawメソッドを使用
$sutents = Student::with([
'scores' => function($query){
$subSql = $query->select(DB::raw('student_id, MAX(date) as latest'))
->groupBy('student_id')
->toSql();
$query->select(DB::raw('scores.student_id,scores.date'))
->joinSub($subSql, 'subT', function ($join) {
$join->on('scores.student_id', '=', 'subT.student_id');
})
->whereRaw('scores.date = subT.latest');
},
])
->get();
もしくはstudentテーブルにjoinさせるか
$subScoreSql = Score::select(DB::raw('student_id, MAX(date) as latest'))
->groupBy('student_id')
->toSql();
$students = Student::select(DB::raw('*'))
->joinSub($subScoreSql, 'subT', function ($join) {
$join->on('students.id', '=', 'subT.student_id');
})
->with([
'scores' => function($query) use ($request) {
$query->orderBy('date');
},
])
->get();
bladeテンプレートで表示
//studentに紐づくscoreは降順で取得しているため[0]番目が最新となる
@foreach ($students as $student)
{{ !empty($student->scores[0]) ? $student->scores[0]->score : 'スコアなし' }}
@endforeach
最後に
DB:raw等で全て素のSQLで書く手段もあります。
Lravel8から1:N のリレーション関係のテーブルを擬似的に、1:1を定義できる機能が追加されたようで、今回の場合studentモデルに$this->hasOne(Score::class)->latestOfMany()のリレーションを実装するだけで、最新のscoreが取れそうです。
参考
Discussion