CODEWITHSUNDEEP
phpmysqlsqllaravel
O P
O P

Reputation: 2365

Query leaderboard with ties

Controller:

$users = DB::table('users')
        ->join('uploads', 'users.id', '=', 'uploads.user_id')
        ->select(DB::raw('users.*, COUNT(*) AS total_uploads'))
        ->orderby('total_uploads', 'desc')
        ->groupby('users.id')
        ->take(5)
        ->get();

View:

<?php $i = 1 ?>
@foreach($users as $user)
<tr>
    <td>{{ $i }}</td>
    <td><a href="user/{{ $user->username }}">{{ $user->username }}</a></td>
    <td>{{ $user->total_uploads }}</td>
</tr>
<?php $i++ ?>
@endforeach

Expected output:

 1  John  99
 2  Mary  78
 3  Jill  57
 3  Bill  57
 5  Hans  56

How can I modify this to account for ties?

Upvotes: 1

Views: 103

Answers (1)

berrberr
berrberr

Reputation: 1960

Try this:

<?php $i = 0;
      $last_count = null;
?>
@foreach($users as $user)
<?php if($user->total_uploads != $last_count) $i++; ?>
<tr>
    <td>{{ $i }}</td>
    <td><a href="user/{{ $user->username }}">{{ $user->username }}</a></td>
    <td>{{ $user->total_uploads }}</td>
</tr>
<?php $last_count = $user->total_uploads; ?>
@endforeach

The counter will only increment if the current user's total_uploads is different from the last user's total_uploads.

Upvotes: 1

Related Questions