Points Ranking in Laravel where points are equal

Question

I am trying to work out how to correct display the rank of points to show a leaderboard.

I currently have the following in my Modal

 public function getPointrankAttribute(){
    $year = Carbon::parse(Carbon::now())->year;

    $pointrank = Points::query()
        ->select('member_id')->selectRaw('SUM(value) as TotalPoints')
        ->where('Year','=', $year)
        ->groupBy('member_id')
        ->orderByDesc('Totalpoints')
        ->get();

    return $pointrank->search(function($points){
        return $points->member_id == $this->id;
    }) + 1;

}

This works well, however it is using the row position in the table to show the rank, so the first record is 1st and the 2nd record shows as 2nd.

However the issue arises when members have the same total points, because under this the rank is based on position on in the table

See the following example

|member_id|totalpoints|
|   12    | 20        |
|    3    |  10       |
|   10    |  10       |
|    5    |   5       |

In this example based on my current solution

Member 12 = 1st, Member 3 = 2nd, Member 10 = 3rd, Member 5 = 4th

What I want is the following

Member 12 = 1st, Member 3 = 2nd, Member 10 = 2nd, Member 5 = 4th

It would be awesome if I could also add "=" as well when we have more than 1 member sharing that position (as in Member 3 and 10 in this case)

Thanks in advance

Answer 1

To get rank for your users you will need to assign rank to distinct total points first, using laravel collection helper you can create separate ranks collection irrespective of member ids like

$collection =collect(
[
  [
    "member_id" => 12,
    "totalpoints" => 20
  ],
  [
    "member_id" => 3,
    "totalpoints" => 10
  ],
  [
    "member_id" => 10,
    "totalpoints" => 10
  ],
  [
    "member_id" => 5,
    "totalpoints" => 5
  ],
]
);

$ranks = $collection->unique('totalpoints')
                    ->values()
                    ->mapWithKeys(function ($item, $index) {
                        return [$item['totalpoints'] => $index + 1];
                    });

Make sure the original collection should be ordered based on total points.

The above will create an array of distinct points as keys and values as ranks based on total points like

Array
(
    [20] => 1
    [10] => 2
    [5] => 3
)

Now you can search your members based on id from original collection and get their respective rank from ranks array like

$id = 10;
$ranks[$collection->firstWhere("member_id", $id)["totalpoints"]];

There is another way to get ranks for your records directly from database if the vendor database supports window functions like RANK()

What I want is the following :Member 12 = 1st, Member 3 = 2nd, Member 10 = 2nd, Member 5 = 4th

To achieve your desired results you just need to remove ->values() from above code so that your results are not re indexed

$ranks = $collection->unique('totalpoints')
                    ->mapWithKeys(function ($item, $index) {
                        return [$item['totalpoints'] => $index + 1];
                    });

Array
(
    [20] => 1
    [10] => 2
    [5] => 4
)

DEMO