Laravel Limit within Join

Question

I have the following in one of my controllers:

$documentReminders = DB::table('employees')
                ->where('department_id',5)
                ->join('employee_attachments', 'employees.id', '=', 'employee_attachments.employeeID')
                    ->whereIn('category', ['Medical Examiners Certificate','Drivers License'] )
                ->orderBy('employee_attachments.endDate')
                ->get();

This does successfully return all of the records I was expecting it too, but the problem here is there are too many (I'll explain).

For each employee record, there could be a few dozen of each of the employee_attachments.category (say one employee could have 8 of both, while one could have 15).

What I would like is to be able to limit the number of employee_attachments returned for each employee to one (1) of each category and that being the most recent record within that category in reference to its employee_attachments.endDate field.

So basically if I had the following employee_attachments table

employeeID     || category                      || endDate
1              || Drivers License               || 2019-01-01
1              || Drivers License               || 2017-01-01
2              || Drivers License               || 2016-01-01
1              || Drivers License               || 2018-01-01
1              || Medical Examiners Certificate || 2017-01-01
1              || Medical Examiners Certificate || 2018-01-01
1              || Medical Examiners Certificate || 2019-01-01
2              || Medical Examiners Certificate || 2017-01-01
2              || Medical Examiners Certificate || 2020-01-01

Then I would want the following returned:

employeeID     || category                      || endDate
1              || Drivers License               || 2019-01-01
1              || Medical Examiners Certificate || 2019-01-01
2              || Drivers License               || 2016-01-01
2              || Medical Examiners Certificate || 2020-01-01

Answer 1

Here is a simple way.

 $documentReminders = DB::table('employees')
        ->join('employee_attachments', 'employees.id', '=', 'employee_attachments.employeeID')
        ->select(['employee_attachments.employeeID','employee_attachments.category',DB::raw('MAX(employee_attachments.endDate) as eDate')])
            ->where('department_id',5)
    ->whereIn('category', ['Medical Examiners Certificate','Drivers License'] )
    ->groupBy('employee_attachments.employeeID','employee_attachments.category')
    ->orderBy('eDate','DESC')
    ->get();

Answer 2

To get the latest record for each employee from employee_attachments , you could introduce a self join with employee_attachments table with additional join criteria where endDate should be highest

$documentReminders = DB::table('employees as e')
                        ->where('department_id',5)
                        ->join('employee_attachments as ea', 'e.id', '=', 'ea.employeeID')
                        ->leftJoin('employee_attachments as ea1', function ($join) {
                            $join->on('ea.employeeID', '=', 'ea1.employeeID')
                                 ->where('ea.category', '=', 'ea1.category')
                                 ->where('ea.endDate', '<', 'ea1.endDate');
                        })
                        ->whereNull('ea1.employeeID')
                        ->whereIn('ea.category', ['Medical Examiners Certificate','Drivers License'] )
                        ->orderBy('ea.endDate')
                        ->select('e.*','ea.*')
                        ->get();    

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

In raw sql it would be

select ea.*
from employee_attachments ea
left join employee_attachments ea1 
     on ea.employeeID = ea1.employeeID
     and ea.category = ea1.category
     and ea.endDate < ea1.endDate
where ea1.employeeID is null
order by ea.employeeID

Demo