CODEWITHSUNDEEP
phplaravel
waqar
waqar

Reputation: 75

how to orderby() count without group in laravel

I have Users table which has under_reference column that represents by whom this user was referred.

I want to order user by having highest under_reference count. How to order by counting under reference 

$data['user'] = User::orderBy('id','DESC')->get();

Upvotes: 1

Views: 474

Answers (5)

FULL STACK DEV
FULL STACK DEV

Reputation: 15941

$data['user'] = User::select(DB::raw('count(*) as total'))
  ->groupBy('under_reference')
  ->orderBy('under_reference','DESC')
  ->get();

Here is the query that can help you. You can get the total users referred by a person and orderBy reference.

Upvotes: 1

vinod gami
vinod gami

Reputation: 67

You can try in this way also worked.

$data['user'] = DB::select('SELECT *` FROM `user` ORDER BY 
CAST(`under_reference` AS decimal) DESC');

You can cast the DB column varchar to decimal in query.

Upvotes: 0

Kannan K
Kannan K

Reputation: 4461

Try this:

$user_info = DB::table('users')
->select('under_reference',DB::raw('count(under_reference) as ur'))
->groupBy('under_reference')
->orderBy('ur','DESC')
->get();

Upvotes: 0

waqar
waqar

Reputation: 75

i created another column totalref with Integer . i coded logic to update this column also with referral bonus and then orderby totalref. and it worked like magic

Upvotes: 0

Mohd.Zafranudin
Mohd.Zafranudin

Reputation: 454

Use Group By for this situation, and sort from it. Here's an example.

$query = [
        'name',
        'email',
        // store count of under_reference as total
        DB::raw('count(under_reference) as total')
    ];

$sorted = User::groupBy('name', 'email')
        ->select($query)
        ->orderBy('total', 'desc') // remember total before, sort it by desc or asc
        ->get();

You can refer from laravel documentation here on raw expressions

The name and email is just an examaple, as you didn't show us what your table's column looks like. But you should get a pretty rough idea

Upvotes: 0

Related Questions