php - Laravel 5 Eloquent : How to group relationship and get rank

Question

基本上我有一个点日志表如下:

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

我希望按总分对用户进行分组并显示他们的排名。

这是我目前在我的 User.php 模型中的内容:

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
        ')
    );
}

然后在我的blade模板中显示如下:

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

Answer 1

不是很优雅，但它有效:

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;
}

或者更优雅的解决方案:

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;
}