[FIXED] How to multiple withcount() columns sum and order by in laravel

Issue

I have to need to order by records based on withcount() function in laravel 6

 $query=User::withCount(['time_spent' =>function($q){
                        $q->select(\DB::raw('COALESCE(sum(COALESCE(time_spent, 0 )),0)'))
                        ->whereDate("created_at", ">=", Carbon::today()->startOfMonth()->toDateString())
                        ->whereDate("created_at", "<=", Carbon::today()->endOfMonth()->toDateString());
                    }])
                    ->withCount(['interactive_time_spent' =>function($q){
                        $q->select(\DB::raw('COALESCE(sum(COALESCE(audio_video_time, 0 ) + COALESCE(chat_time,0)),0)'))
                        ->whereDate("created_at", ">=", Carbon::today()->startOfMonth()->toDateString())
                        ->whereDate("created_at", "<=", Carbon::today()->endOfMonth()->toDateString());
                    }])
                    ->orderBy("(interactive_time_spent_count + time_spent_count)",$sortOder)->get();

In this code, I have two withCount() functions and I need to order By based on that two-column sum before get(). It works when order by using one column but if I use two-column then it returns an unknown column. Is it possible or not?

Solution

In this case you need to use orderByRaw for your custom expressions

->orderByRaw("(interactive_time_spent_count + time_spent_count) ".$sortOder)

Answered By - M Khalid Junaid

Answer Checked By - Marilyn (PHPFixing Volunteer)