我有一個(gè)users包含列country和用戶(hù)belongsToMany Categories 的表。用戶(hù)還hasMany與Payments表有關(guān)系。我在嘗試著：按國(guó)家/地區(qū)對(duì)用戶(hù)進(jìn)行分組，顯示每個(gè)國(guó)家/地區(qū)最受歡迎的類(lèi)別（這已經(jīng)解決）對(duì)每個(gè)分組國(guó)家/地區(qū)的表amount中的列求和payments每次我嘗試加入payments表格時(shí)，它都會(huì)返回多個(gè)結(jié)果，這使得總和完全錯(cuò)誤。我相信我沒(méi)有使用正確的查詢(xún)。以下是表結(jié)構(gòu)：用戶(hù)id | name | country1 | UserA | Canada2 | UserB | USA3 | UserC | Canada類(lèi)別id | Name1 | Housing2 | Cooking類(lèi)別_用戶(hù)id | category_id | user_id1 | 1 | 12 | 2 | 23 | 1 | 3付款id | amount | user_id1 | 500 | 12 | 200 | 23 | 150 | 14 | 100 | 3第一個(gè)問(wèn)題已使用以下代碼解決：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(); });結(jié)果是這樣的：[ { "country": "Canada", "name": "Housing", "total": 2 }, { "country": "USA", "name": "Cooking", "total": 1 }]我想從付款中獲得 SUM(amount) 作為 total_payments。任何幫助，將不勝感激。謝謝。
1 回答
繁星淼淼
TA貢獻(xiàn)1775條經(jīng)驗(yàn) 獲得超11個(gè)贊
我找不到減少查詢(xún)數(shù)量的方法，但在我的本地測(cè)試中進(jìn)行了后續(xù)工作。
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 關(guān)注
- 171 瀏覽
添加回答
舉報(bào)
0/150
提交
取消