我有一個users包含列country和用戶belongsToMany Categories 的表。用戶還hasMany與Payments表有關系。我在嘗試著：按國家/地區對用戶進行分組，顯示每個國家/地區最受歡迎的類別（這已經解決）對每個分組國家/地區的表amount中的列求和payments每次我嘗試加入payments表格時，它都會返回多個結果，這使得總和完全錯誤。我相信我沒有使用正確的查詢。以下是表結構：用戶id | name | country1 | UserA | Canada2 | UserB | USA3 | UserC | Canada類別id | Name1 | Housing2 | Cooking類別_用戶id | category_id | user_id1 | 1 | 12 | 2 | 23 | 1 | 3付款id | amount | user_id1 | 500 | 12 | 200 | 23 | 150 | 14 | 100 | 3第一個問題已使用以下代碼解決：return User::leftJoin('category_user as cu', 'users.id', '=', 'cu.user_id') ->join('categories as c', 'cu.category_id', '=', 'c.id') ->groupBy(['country', 'c.name']) ->get([ 'users.country', 'c.name', DB::raw('count(*) as total') ]) ->groupBy('country') ->values() ->map(function (Collection $elt) { return $elt->sortByDesc('total')->first(); });結果是這樣的：[ { "country": "Canada", "name": "Housing", "total": 2 }, { "country": "USA", "name": "Cooking", "total": 1 }]我想從付款中獲得 SUM(amount) 作為 total_payments。任何幫助，將不勝感激。謝謝。
1 回答
繁星淼淼
TA貢獻1775條經驗 獲得超11個贊
我找不到減少查詢數量的方法，但在我的本地測試中進行了后續工作。
public function foobar()
{
$popular = User::leftJoin('category_user as cu', 'users.id', '=', 'cu.user_id')
->join('categories as c', 'cu.category_id', '=', 'c.id')
->groupBy(['country', 'c.name'])
->get([
'users.country',
'c.name',
DB::raw('count(*) as total')
])
->groupBy('country')
->values()
->map(function (Collection $elt) {
return $elt->sortByDesc('total')->first();
}); // this one is same (to get popular category)
// this one for payments and number of users from that country
return User::leftJoin('payments as p', 'users.id', '=', 'p.user_id')
->groupBy('country')
->get(['country', DB::raw('sum(p.amount) as total'), DB::raw('count(distinct(users.id)) as userCount')])
->map(function ($col) use ($popular) { // merging two collections into one
$col->name = $popular->where('country', $col->country)->first()->name;
return $col;
});
}
它打印出這樣的東西；
[
{
"country": "Canada",
"total": "1150",
"userCount": 2,
"name": "Housing"
},
{
"country": "Italy",
"total": "100",
"userCount": 1,
"name": "Kitchen"
},
{
"country": "USA",
"total": "200",
"userCount": 1,
"name": "Cooking"
}
]
- 1 回答
- 0 關注
- 162 瀏覽
添加回答
舉報
0/150
提交
取消