Laravel get the position/rank of a row in collection

Question

I have a leaderboards and leaderboard_scores table.

leaderboard_scores has columns: id, leaderboard_id, player_id, score.

In Leaderboard model, I defined a hasMany relationship with LeaderboardScore:

public function scores()
{
    return $this->hasMany('App\LeaderboardScore')->orderBy('score', 'desc');
}

Now I want to get the ranking of a player in a particular leaderboard.

So in controller, I do a loop to find the position given a player_id:

$score = $leaderboard->scores;

$scoreCount = $scores->count();
$myScore = $scores->where('player_id', $request->query('player_id'));

if ($myScore) { // If got my score
    $myRank = 1;
    for ($i = 0; $i < $scoreCount; $i++) {
        if ($scores[$i]->player_id == $request->query('player_id')) {
            $myRank = $i + 1;
            break;
        }
    }
    // Output or do something with $myRank
}

It works fine, but I am worried about the performance, when I have hundred thousand of players and they constantly getting their ranking. The for loop seems not a good option.

Should I use raw database query? Or any better idea?

Answer 1

Here some ideas

• Instead of calculating ranks each time you get request, then loop through all data. you can just store players rank either in database or cache. You only need to calculate them when new player added/deleted or score changed.

Check For Caching: https://laravel.com/docs/5.4/redis

• if you still want to calculate it everytime you can use mysql rank function which is more optimized than a primitive loop

Mysql Rank : http://www.folkstalk.com/2013/03/grouped-rank-function-mysql-sql-query.html