📘

グループ毎に最新のレコードを取得する Laravel SQL

2 min read

はじめに

最新日付のレコードを取得、表示させる方法について記述します。環境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で相関問い合わせにより生徒毎に最新の1件を取得

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が取れそうです。

参考

https://www.risewill.co.jp/blog/archives/3483
https://qiita.com/iitenkida7/items/c843f3758c01b2db941c

Discussion

ログインするとコメントできます