How to sort by eager loading results in Laravel?

Question

I am extremely new to eager loading and have run into what I assume to be a simple problem of me just not understanding it. I reviewed some of the materials here and have looked around but can't seem to get maybe the right terminology for what I am asking.

At the moment in my equipment model I have the following relationship:

public function registrationsExpireLatest()
{
       return $this->hasOne(EquipmentLicense::class,'equipmentID','id')
    ->orderByDesc('expirationDate');
}

This works perfectly, but say I want to put it on an index blade under an @foreach, I get everything I need, including the expiration date using the code below:

        @foreach ($equipments as $equipment)

             <tr>
                 <td><a href="/origins/{{$equipment['id']}}">{{$equipment['unit_id']}}</a></td>
                 <td>{{\Crypt::decryptString($equipment->licensePlate)}}</td>
                 <td>@if (count($equipment->registrationsExpireLatest))
                     {{$equipment->registrationsExpireLatest->expirationDate}}
                    @endif</td>
            </tr>

        @endforeach

Coming from this controller code:

    $equipments = Equipment::with(['registrationsExpireLatest' => function ($query) {
                                    $query->orderBy('expirationDate', 'asc');
                                }])
                                ->where([
                                    ['unit_type','<',3],
                                    ['active','=',1]
                                    ])
                                ->limit(10)
                                ->get();

Which all outputs like this:

11      086-YRR     2015-05-31
26      062-XWE     2018-11-30
33      880-HNV     2018-04-30
39      820-YYT     2018-01-31
203     279-WWU     2013-12-31
31      BMR 199     2018-04-30
UNK3    997-WLH     2011-09-30
1       957-VDN     2018-05-31
1096    187-MFF     2015-01-31
2105    154-CLU     2018-01-31 

As you can see by my controller, I tried sorting already in what I thought was the proper way put out here: https://laravel.com/docs/5.3/eloquent-relationships#constraining-eager-loads

But as you can see from my results table, they are out of order according to the expiration date.

I'd like it to go earlier (as in 2011) to later (as in 2018) by date. Is there a way of doing this?

Answer 1

What you are currently doing is sorting the related collection, i.e. if registrationsExpireLatest was a one-to-many relation, the related models would have been sorted in ascending order by their expirationDate for each of your Equipments. As far as I can see from your model, you already join the latest entity anyway.

To sort the Equipments on a relation is normally not that hard, you just have to join the related table. But in your case, where you have multiple elements in the related table per Equipment, the join is a bit more complex. I think something like the following could work:

Equipment::where([
        ['unit_type','<',3],
        ['active','=',1]
    ])
    ->join(DB::raw('(SELECT equipmentID, MAX(expirationDate) AS latest FROM equipment_license GROUP BY equipmentID) AS tmp'), 'equipment.id', '=', 'tmp.equipmentID')
    ->orderBy('tmp.latest')
    ->select('equipment.*', 'tmp.latest')
    ->limit(10)
    ->get();

Answer 2

(based on Namoshek's answer)

The JOIN requires an alias:

Equipment::where([
        ['unit_type','<',3],
        ['active','=',1]
    ])
    ->join(DB::raw('(SELECT equipmentID, MAX(expirationDate) AS latest FROM equipment_license GROUP BY equipmentID) as license'), 'equipment.id', '=', 'license.equipmentID')
    ->orderBy('license.latest')
    ->limit(10)
    ->get();