Getting rank of row in OrderBy desc Eloquent query, How can i make this query work in laravel 5.5 eloquents?)

Question

I am trying to give my users a ranking number for my Laravel hiscores pagination table.

This is the MySQL query I found working. I am trying to put make this work as a Laravel eloquent query.

select @i := @i + 1 ranking, t.*
from (select @i:=0) initvars, users t  ORDER BY wins DESC;

My Laravel eloquent query right now:

$ranking = User::orderBy('wins', 'DESC')->paginate(10);

Answer 1

This is my solution.

I first added this function to my User Modal Class.

public function getRanking(){
   $collection = collect(User::orderBy('wins', 'DESC')->get());
   $data       = $collection->where('id', $this->id);
   $value      = $data->keys()->first() + 1;
   return $value;
}

Now in my view I run my getRanking() function.

@foreach($ranking as $key => $rankings)
    <tr>
        <td>{{ $rankings->getRanking() }}</td>
        <td><a href="{{ route('profileView', ['id' => $rankings->id]) }}">{{ $rankings->username }}</a></td>
        <td>{{ $rankings->wins }}</td>
        <td>{{ $rankings->losses }}</td>
    </tr>
@endforeach

I am using my array keys to determine the user ranking.

Answer 2

$table = (new User)->getTable();
$select = \DB::raw("@i := coalesce(@i + 1, 1) ranking, {$table}.*")
$users = User::select($select)->orderByDesc('wins')->paginate(5);

In the above, you need to select table.* again because even if you use addSelect instead of select eloquent loses the original column selection. I'm not sure if it's a bug or intended behaviour, but i'll investigate.

To clarify, if you just do

$raw = \DB::raw("@i := coalesce(@i + 1, 1) ranking");
$model->addSelect($raw); // this way
$model->select($raw); // or this way

The only field you will see on your model is ranking.

--

What might be of note is that if you're paginating based on something which fluctuates like ranking you may see some unexpected behaviour. For example if page 1 shows users a, b, c, d, e (rank 1-5 respectively) and user f climbs the ranking to position 5 before the visitor clicks to page 2, they'd see user e (now rank 6) again and not see user f (now rank 5) when on page 2.