Harry
Harry

Reputation: 139

How to sort users in laravel 5

In laravel 5.2, I want to get users by order country, then posts_count and then images_count. For that I have used below query.

User::with(['profiles' => function($query) use ($country){
       $query->orderBy(DB::raw('case when country="'.$country.'" then 1 else 2 end'))->orderBy('country', 'ASC');
                        }])
      ->withCount('posts')
      ->withCount('images')
      ->orderBy('posts_count', 'desc')
      ->orderBy('images_count', 'desc')
      ->get();

But this query sort users by posts_count, then images_count and then country. Please guide how can I fix the order priority country first in the query.

Upvotes: 0

Views: 233

Answers (1)

Gaurav Gupta
Gaurav Gupta

Reputation: 1698

try this

User::with(['profiles' => function($query) use ($country){
                               $query->orderBy(DB::raw('case when country="'.$country.'" then 1 else 2 end'));
                        }])
                      ->withCount('posts')
                      ->withCount('images')
                      ->orderBy('country', 'ASC')
                      ->orderBy('posts_count', 'desc')
                      ->orderBy('images_count', 'desc')
                      ->get();

different way

DB::table('user')->select(DB::raw("(case when country!='".$country."' then 1 else 2 end)as country"),DB::raw("(count(post))as post"),DB::raw("(count(images))as images"))
      ->orderBy('country', 'ASC')
      ->orderBy('posts', 'desc')
      ->orderBy('images', 'desc')
      ->get();

Upvotes: 1

Related Questions