Laravel get most recent date of joined table

Question

I'm performing this query with Laravel's query builder with a left join with the two tables linking on the column s_nid. With the table I'm left joining to conference_report there are many rows with one s_nid and I only want to get back the row with the most recent date. I have been looking at Groupwise Maximum with some examples here on SO which is new to me and I think is what I need to complete the query but I'm unsure how to implement it on the table I'm joining to.

$data['students'] = DB::table('educational_report')
        ->select('educational_report.s_nid as s_nid'
                ,'educational_report.name as name'
                ,'educational_report.file_no as file_no'
                ,'educational_report.university as university'
                ,'conference_report.conference_end_date as conference_end_date'
                ,'student_status.status as status')
        ->leftJoin('student_status', 'student_status.s_nid', '=', 'educational_report.s_nid')
        ->leftJoin('conference_report', 'conference_report.s_nid', '=', 'educational_report.s_nid')
        ->distinct()
        ->groupBy('s_nid')
        ->paginate(10);

Possibly useful links:

Performing a Groupwise Maximum in Laravel 4

Groupwise maximum

http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

score 1 · Answer 1 · answered Sep 05 '14 at 11:39

I think I was over complicating things for no need. I managed to work it out using a nested query on the join as so:

$data['students'] = DB::table('educational_report')
        ->select('educational_report.s_nid as s_nid'
                ,'educational_report.name as name'
                ,'educational_report.file_no as file_no'
                ,'educational_report.university as university'
                ,'cr2.conference_end_date as conference_end_date'
                ,'student_status.status as status')
        ->leftJoin('student_status', 'student_status.s_nid', '=', 'educational_report.s_nid')
        ->leftJoin(DB::raw('(select s_nid, MAX(conference_end_date) conference_end_date
                                from conference_report GROUP BY s_nid) as cr2'), function ($join) {
                                    $join->on ( 'cr2.s_nid', '=', 'educational_report.s_nid' );
                    })
        ->paginate(10);

It appears to be working correctly but I'd be keen for any answers that may correct what I'm doing or provide a better way of doing it. Thanks :)

Laravel get most recent date of joined table

1 Answers1