Laravel Orderby not working inside Eloquent whereHas relationship?

Question

Please have look on below code.

 $serviceNew = CompletedService::selectRaw('gallons_collected as total,account_id,id,service_provider_id,service_id,service_date')
            ->where("service_id", '1')->where('service_provider_id', $service_privider_id)->whereMonth('service_date', $month)->whereYear('service_date', $year)
            ->with('account')
            ->whereHas('account', function($qs) {
                $qs->orderBy('restaurant_name', 'DESC');
            })
            ->get();

I have multiple records in "CompletedService" and there is one parent id account_id which in account table. and i made with on account.

Already ASC and DESC tried.

Try to order by in whereHas but it's not affect on any records. Below is model relationship.

 public function account() {
    return $this->hasOne('App\Account', 'id', 'account_id');
}

i don't need to orderby in model because i used this relation in multiple time and need only order by in this single query.

And one completed service records relations have only one account. So basically i need to sort completed service records on the account field.

Output

Tables

CompletedService

|id| account_id|service_id|service_provider_id|gallons_collected|service_date
|1 | 2         | 1        | 9                 | 50              | 2017-08-29

Accounts

| id | restaurant_name|

Every help will be appreciated. Thanks in advance.!

Answer 1

You need to use alternate solution for this. First you need to fetch your data/collection and then need to apply order by.

Laravel provides some functions, using that you can sort your collection.

https://laravel.com/docs/5.6/collections#method-sortby

Use sortByDesc (for descending). So here is your code :

$serviceNew = CompletedService::selectRaw('gallons_collected as total,account_id,id,service_provider_id,service_id,service_date')
            ->where("service_id", '1')->where('service_provider_id', $service_privider_id)->whereMonth('service_date', $month)->whereYear('service_date', $year)
            ->with('account')
            ->whereHas('account')
            ->get()
            ->sortByDesc('account.restaurant_name');