CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
Tom Parkes
Tom Parkes

Reputation: 43

Laravel 5 Eloquent: How to group relationship and get rank

Basically I have a point log table as follows:

user_id | points
1       | 10
2       | 20
1       | 30
1       | 4
2       | 6
6       | 8

and I am looking to group the users by their total points and display their ranking.

This is what I have so far in my User.php model:

public function getPointRankAttribute() {
  return $this->hasMany('App\PointLog')
      ->select(DB::raw('
          SELECT s.*, @rank := @rank + 1 rank FROM (
            SELECT user_id, sum(points) TotalPoints FROM t
            GROUP BY user_id
          ) s, (SELECT @rank := 0) init
          ORDER BY TotalPoints DESC
        ')
    );
}

and then display in my blade template as follows:

Your point rank: {{ $user->pointRank }}

Upvotes: 1

Views: 1835

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Not very elegant, but it works:

public function getPointRankAttribute() {
    $ranks = DB::select('
      SELECT s.*, @rank := @rank + 1 rank
      FROM (
        SELECT user_id, sum(points) TotalPoints
        FROM pointLogs
        GROUP BY user_id
      ) s, (SELECT @rank := 0) init
      ORDER BY TotalPoints DESC
    ');
    return collect($ranks)->where('user_id', $this->id)->first()->rank;
}

Or a more elegant solution:

public function getPointRankAttribute() {
    $ranks = PointLog::query()
        ->select('user_id')->selectRaw('SUM(`points`) TotalPoints')
        ->groupBy('user_id')
        ->orderByDesc('TotalPoints')
        ->get();
    return $ranks->search(function($pointLog) {
        return $pointLog->user_id == $this->id;
    }) + 1;
}

Upvotes: 2

Related Questions