CODEWITHSUNDEEP
laraveleloquent
knubbe
knubbe

Reputation: 1182

Sort by latest record column from relations

I have two tables (models) Vehicle and Registration. Vehicle has many registrations and every registrations has start_date and expiry_date and I need to get all vehicles with last registration sort by expiry_date. This is mine Vehicle model part:

/**
     * @return HasMany
     */
    public function registrations() :HasMany
    {
        return $this->hasMany(Registration::class);
    }

    /**
     * @return HasOne
     */
    public function activeRegistration() :HasOne
    {
        return $this->hasOne(Registration::class)->latest();
    }

and I try to solve like this:

Vehicle::with('activeRegistration')->get()->sortBy('activeRegistration.expiry_date')->take(5) // I need only 5 records

but this is not working as I expected. This is part of mine blade file:

@foreach($registrationsVehicle as $vehicle)
    <tr>
        <td>{{ $vehicle->registration }}</td>
        <td>{{ $vehicle->vehicleBrand->name }}</td>
        <td>{{ $vehicle->model }}</td>
        <td>{{ optional($vehicle->activeRegistration)->start_date }}</td>
        <td>{{ optional($vehicle->activeRegistration)->expiry_date }}</td>
    </tr>
@endforeach

I get data but it's not order correctly.

Upvotes: 0

Views: 129

Answers (3)

knubbe
knubbe

Reputation: 1182

At the end I solve like this:

return Vehicle::join('registrations', 'vehicles.id','=','registrations.vehicle_id')
            ->has('activeRegistration')
            ->orderBy('registrations.expiry_date')
            ->limit(5)
            ->get();

Upvotes: 0

TsaiKoga
TsaiKoga

Reputation: 13394

You need to add field in latest method:

    public function activeRegistration() :HasOne
    {
        return $this->hasOne(Registration::class)->latest('expiry_date');
    }

If you want to use with('registration'), you need to use closure like this:

$vehicles = Vehicle::with(['registration' => function($relation) {
    $relation->orderBy('expiry_date', 'DESC')->first();
}])->take(5)->get();

For Laravel 5.7 and onwards

if you want to get last registration, you can just use latest('expiry_date'),

it will automatically convert to order by expiry_date desc

$vehicles = Vehicle::with(['registration' => function($relation) {
    $relation->latest('expiry_date')->first();
}])->take(5)->get();

Upvotes: 3

Đỗ Nhật Quang
Đỗ Nhật Quang

Reputation: 842

Get all then use sortBy() function in collection

 Vehicle::with(['activeRegistration'])
        ->all()
        ->sortByDest(function ($vehicle) {
            return $vehicle->activeRegistration->expiry_date;
        });

Upvotes: 0

Related Questions