带连接表的多列 SUM - Laravel 5.2

Question

我有两个表winners和check_winners。

赢家：

id     check_winner_id      username     win_number ....
 1                   1           xxx              1
 2                   2           xxx              1
 3                   3           yyy              1
 4                   4           yyy              1

check_winners：

id       user_id      chance_of_win....
 1             1                  1
 2             1                  1
 3             2                  1
 4             2                  1
 5             2                  1
 6             2                  1
 7             2                  1
 8             2                  1

现在我想join两个表，并希望对chance_of_win列和win_number列分组username求和。我已经尝试了一种方法，但它没有给出win_number总和的确切结果。我怎样才能做到这一点？

$winners = DB::table('winners')
            ->groupBy('winners.username')
            ->leftJoin('check_winners', 'winners.id', '=', 'check_winners.user_id')
            ->selectRaw('*, sum(check_winners.chance_of_win) as chance_sum, sum(winners.win_number) as win_sum')         
dd($winners);

有了这个，我得到输出：

array:2 [▼
  0 => {#241 ▼
    +"id": 1
    +"check_winner_id": 1
    +"username": "xxx"
    +"chance_sum": "2"
    +"win_sum": "2"
  }
  1 => {#242 ▼
    +"id": 2
    +"check_winner_id": 2
    +"username": "yyy"
    +"chance_sum": "6"
    +"win_sum": "6"
  }
]

但是对于两个array都应该2 win_sum.我哪里做错了？提前谢谢。

Answer 1

你应该试试这个：

$winners = DB::table('winners')
            ->leftJoin('check_winners', 'winners.id', '=', 'check_winners.user_id')
            ->selectRaw('*, sum(check_winners.chance_of_win) as chance_sum, sum(winners.win_number) as win_sum')
            ->groupBy('winners.username')       
dd($winners);

更新的答案

$winners = DB::table('winners')
            ->select('*', DB::raw("SUM(check_winners.chance_of_win) as chance_sum"), DB::raw("SUM(winners.win_number) as win_sum"))
            ->leftJoin('check_winners', 'winners.id', '=', 'check_winners.user_id')
            ->groupBy('winners.username')