【Laravel】複数のテーブルをJOIN→GROUP BYでグループ化→特定のレコードを取得

前提条件

親テーブル：classes

id	name
1	A
2	B
3	C

子テーブル：students

id	number	name	class_id
1	1	AAA	1
2	2	BBB	1
3	1	CCC	2
4	2	DDD	2
5	1	EEE	3
6	3	FFF	2
やりたいこと：各クラスの中で出席番号(number)が一番最後の生徒のデータを取得
MySQL

SQL

SELECT * FROM classes 
    LEFT JOIN students 
    ON classes.id = students.class_id
WHERE students.number IN(
    SELECT MAX(students.number) 
    FROM students 
    GROUP BY students.class_id
);

Laravel SQL直書き

$sql = 'students.number IN(
    SELECT MAX(students.number)
    FROM students
    GROUP BY students.class_id
)';

$student = DB::table('classes')
    ->leftJoin('students', 'classes.id','=','students.class_id')
    ->whereRaw($sql);

Laravel サブクエリ

$student = DB::table('classes')
    ->leftJoin('students', 'classes.id','=','students.class_id')
    ->whereIn('students.number', function($sub) {
        $sub->from('students')
            ->select(DB::raw('max(students.number)'))
            ->groupBy('students.class_id');
    });