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

You write "limit the number of employee_attachments returned for each employee to one (1)", but in your sample data you limit the number of `employee_attachments` to one per *employee and category*. — Jonas Staudenmeir, Jul 18 '18 at 13:42
@JonasStaudenmeir - Sorry typo, the sample data is correct, and I will fix the question here in a moment. — Matthew, Jul 18 '18 at 13:56

Mohamed Akram · Accepted Answer · 2018-07-18T14:07:27.803

2

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();

edited Jul 18 '18 at 14:07

answered Jul 18 '18 at 06:27

Mohamed Akram

2,244
15
23

At this time I get a Syntax error: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column `truckin.employee_attachments.endDate` which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode = only_full_group_by - – Matthew Jul 18 '18 at 13:59
remove the `->orderBy('employee_attachments.endDate','DESC')` clause and try again, it should work, – Mohamed Akram Jul 18 '18 at 14:03
I'll give you that, it does work but another issue crops up in that I do require the orderBy, as it is technically a list of upcoming expirations, it needs to show the soonest to latest of the coming attachments in the returned array. – Matthew Jul 18 '18 at 14:06
@Matthew i edit the answer above and added an allias for date and modified the `orderBy` , check that and let me know – Mohamed Akram Jul 18 '18 at 14:08

M Khalid Junaid · Answer 2 · 2018-07-18T06:09:46.297

0

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

edited Jul 18 '18 at 06:09

answered Jul 18 '18 at 05:59

M Khalid Junaid

63,861
10
90
118

At this moment, I get the following error returned, though I did see your example and know that it should be close: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'truckin.employee_attachments ea' doesn't exist (SQL: select `e`.*, `ea`.* from `employees` as `e` inner join `employee_attachments ea` on `e`.`id` = `ea`.`employeeID` left join `employee_attachments` as `ea1` on `ea`.`employeeID` = `ea1`.`employeeID` and `ea`.`category` = ea1.category and `ea`.`endDate` < ea1.endDate where `department_id` = 5 and `ea1`.`employeeID` is null and `ea`.`category` in (Medical Examiners Ce... – Matthew Jul 18 '18 at 06:09
@Matthew It might be due to missing `AS` keyword in `join(...)` part check updated answer – M Khalid Junaid Jul 18 '18 at 06:10
Alright, now it is coming through just fine but it still returns multiple copies of the same category record for an employee. I have a meeting to go to in a few so I will work on this later, I just figured mention that. Thank you for your help though too! – Matthew Jul 18 '18 at 06:15
@Matthew , check my answer bellow and let me know your feedback – Mohamed Akram Jul 18 '18 at 06:30
@Matthew It there a chance that there can be morethan 1 rows for single employee and same category with same highest date in employee_attachments table? – M Khalid Junaid Jul 18 '18 at 06:38
@MKhalidJunaid - No, all of the records that were returned all have different endDates. – Matthew Jul 18 '18 at 13:55
@Matthew can you add your sample data set here http://sqlfiddle.com/#!9/8dc7c0/2 and reproduce same issue – M Khalid Junaid Jul 18 '18 at 15:06

Laravel Limit within Join

2 Answers2