Laravel: eloquent orderBy hasOne relation column using with

Question

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.

Answer 1

You can achieve this by:

  // eager loading
  $orders = \App\Orders::with( [ 'participant'=> function( $q ) {
             $q->orderBy( 'last_name', 'asc/desc' );
   } ] )->get();

Answer 2

It looks a bit redundant, but I've sorted it out by using join. A quite ugly though. Note the select part. Without it all gets messed up

          $orders = \App\Orders::select('orders.*')
                ->with([
                    '....',
                    'participant',
                    'participant.documents',
                    'participant.participantParent',
                    'participant.country',
                    '...'
                    ])
                ->join('order_participants', function ($join) {
                    $join->on('order_participants.order_id', '=', 'orders.id');
                })
                ->where('orders.user_id', $user->id)  
                ->where(function($q) {
                    $q->where('orders.status', '!=', 'completed')
                    ->orWhereNull('orders.status');
                })   
                ->orderBy('order_participants.last_name')
                ->orderBy('order_participants.first_name')
                ->get();

Since my query is a bit more complicated than in the question above, i'm posting entire code as an example. As I understand, join has to come before where statements