mardi 17 juillet 2018

Laravel Limiit within Join

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) and that being the most recent within the 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

I'd appreciate the help - Matt



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire