How to order records with having a relationship comes last in query result

Question

I have two tables named calls and call_histories. For example, the following is my query.

$calls = Call::select([
    'calls.id as id', 'calls.reserved_at', 'calls.app_id',
    'calls.call_type_id', 'call_histories.call_id'
])->leftJoin('call_histories', 'calls.id', '=', 'call_histories.call_id')
    ->whereIn('call_type_id', $callTypesIds)
    ->where(DB::raw("(DATE_FORMAT(reserved_at,'%Y-%m-%d'))"), '<=', 
        Carbon::now()->format('Y-m-d'))
    ->where('app_id', $callAppId)
    ->free()
    ->orderBy('reserved_at')
    ->take(100);

I want to know if a call has any call_histories orders as last records in query result. How can I do that?

Answer 1

Since call_histories.call_id will be null if not found, you can sort by that:

$calls = Call::select([
            'calls.id as id', 'calls.reserved_at', 'calls.app_id', 'calls.call_type_id', 'call_histories.call_id'
        ])->leftJoin('call_histories', 'calls.id', '=', 'call_histories.call_id')
            ->whereIn('call_type_id', $callTypesIds)
            ->where(DB::raw("(DATE_FORMAT(reserved_at,'%Y-%m-%d'))"), '<=', Carbon::now()->format('Y-m-d'))
            ->where('app_id', $callAppId)
            ->free()
            ->orderBy('reserved_at')
            ->orderBy('call_id')
            ->take(100);