Laravel - How to order by a related attribute in hasMany

user_3911584 0
Viewed 57

I have two entities: Producers and Protocols. Producers has many Protocols.

Problem

Select all producers ordered by their last protocol creation date.

Solution

In order to get this, I tried:

$producers = Producer::join('protocol', 'protocol.producer_id', '=', 'producer.id')
  ->orderBy('protocol.created_at', 'desc');

But just didn't work as expected mainly because I even limited the last protocol. How can I achieve this using Laravel Eloquent?

eloquent laravel mysql php
2 Answers

Use groupBy():

$producers = Producer::select('producer.*')
    ->join('protocol', 'protocol.producer_id', '=', 'producer.id')
    ->groupBy('producer.id')
    ->orderByRaw('max(protocol.created_at) desc');
user_48485870

first at all describe a relationship for last protocol on Producer model

public function last_protocol(){
    return $this->hasOne(Protocol::class)->latest();
}

then, in your controller

$producers = Producer::with('last_protocol')->get()->sortByDesc('last_protocol.created_at');
user_17317800
Related