Order by relevance based on multiple conditions using Laravel-CodePudding

I am having an issue while using order by based on multiple conditions. User with most filled information should show up top and then the one's with less filled information.

 $users = User::where('status',1)  
 ->withCount('reviews')
 ->with('reviews','about')
 ->orderByRaw("CASE WHEN is_native != '0' AND photo != '' THEN 0  ELSE 1 END")// how i can match the about us relationship value here? means if user have added about intro then it should come first and reviews count? 
 ->paginate(10);

Here is my About Relationship on User

public function about()
{
        return $this->hasOne('App\UserAbout', 'user_id')->select('about');
}

NOTE: i am trying to do it with CASE, if there is any other good option you can please point out that.

Thank you

CodePudding user response：

this means that you have to orderby about's count and then by review count, that will get the result you want:

 $users = User::where('status',1)  
 ->withCount(['reviews','about'])
 ->with('reviews','about')
 ->orderByRaw('about_count desc,reviews_count desc')
 ->paginate(10);

now user with 'about' will have about_count=1 others will have about_count =0

CodePudding user response：

As @OMR suggested you can do that. But you don't need to use raw Query

$users = User::where('status',1)  
 ->withCount(['reviews','about'])
 ->with('reviews','about')
 ->orderBy('about_count','desc')
 ->orderBy('reviews_count','desc')
 ->paginate(10);