我想優化這 3 個查詢，使我能夠根據日期標準（本周、上周、自開始以來）顯示在我們網站上發布最多照片的用戶。
用debug bar測的查詢時間是1.5秒，真的好長！你知道怎么優化嗎？
public function show()
{
$currentWeek = User::whereHas('pictures')
->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]))
->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])])
->orderBy('pictures_count', 'DESC')
->limit(10)
->get();
$lastWeek = User::whereHas('pictures')
->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()]))
->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()])])
->orderBy('pictures_count', 'DESC')
->limit(10)
->get();
$overall = User::whereHas('pictures')
->whereHas('pictures')
->withCount('pictures')
->orderBy('pictures_count', 'DESC')
->limit(10)
->get();
return view('users.leaderboard', [
'currentWeek' => $currentWeek,
'lastWeek' => $lastWeek,
'overall' => $overall,
]);
}
uj5u.com熱心網友回復：
首先，你已經whereHas在圖片關系上呼叫了兩次，所以你可以擺脫不合格的呼叫。
$currentWeek = User::whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()]))
->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()])])
->orderBy('pictures_count', 'DESC')
->limit(10)
->get();
這減少了 SQL 查詢：
select `users`.*, (
select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
from `users`
where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `pictures`.`deleted_at` is null)
and exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
and `users`.`deleted_at` is null
order by `pictures_count` desc
limit 10
對此：
select `users`.*, (
select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
from `users`
where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
-- no second where exists clause
and `users`.`deleted_at` is null
order by `pictures_count` desc
limit 10
現在，您在where子句中只有一個條件。它選擇在指定日期范圍內擁有圖片的用戶。看起來更好，對吧？
但是，您已經在使用withCount閉包，因此您只計算日期范圍內的圖片。如果條件不匹配會發生什么？它回傳零。由于無論如何您都是按計數進行反向排序，因此另一個呼叫whereHas也可以進行。
$currentWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()])])
->orderBy('pictures_count', 'DESC')
->limit(10)
->get();
現在你的 SQL 看起來像這樣：
select `users`.*, (
select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
from `users`
where `users`.`deleted_at` is null
-- no where exists clauses at all any more
order by `pictures_count` desc
limit 10
And it should run much faster. This does change your data somewhat; the resulting collection will always have 10 items, even if some of them are zero. If you don't want zeros in your leaderboard, just filter them out of the collection.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/334335.html