[FIXED] Laravel: eloquent orderBy hasOne relation column using with

Issue

I have a model Orders with a hasOne relation participant.

public function participant()
{
    return $this->hasOne('App\OrderParticipant', 'order_id');
}

I need to retrieve the Orders collection sorted by participant.last_name

My approach

$orders = \App\Orders::with(['participant',])
              ->where('user_id', $user->id)  
              ->orderBy('participant.last_name')
              ->get();

Fails with :

Undefined table: 7 ERROR: missing FROM-clause entry for table \"participant\"\nLINE 1: ...1

I've tried to sort it after collected

return $orders->sortBy('participant.last_name');

But this doesn't sort at all

BTW I'm using postgres

Thanks.

Solution

You can't order by hasOne directly, you have to use join

$orders = \App\Orders::with([
                'participant',                    
                ])
            ->where('orders.user_id', $user->id)  
            ->join('participants', 'orders.user_id', '=', 'participants.id')
            ->orderBy('participants.last_name')
            ->select('orders.*','participants.id','participants.last_name')
            ->get();

Answered By - Anas Bakro