讨论数量:
$items = $event->getOrder()->items()->with(['product'])->get();
foreach ($items as $item) {
// 更新商品的评分和评价数
$item->product->update([
'rating' => bcdiv(
bcadd(
bcmul($item->product->rating, $item->product->review_count, 2),
$item->rating,
2
),
$item->product->review_count + 1,
2
),
'review_count' => Db::raw('review_count + 1'),
]);
}
为啥知道 n + 1,还用 foreach 呢,把 product_id 取出来当成数组,用 in 查询
这位朋友说的对,可以用whereIn查询出一组数据,然后使用数组id匹配的方式来进行更新。还补充一句,如果量大,可以使用分块+队列的方式,结合whereIn,就不用担心新能问题了。
如果是数据量大,就不要用多层嵌套with取数据了,还不如用join查
public function handle(OrderReviewed $event)
{
//取出当前子订单所有商品id
$productIdArr = OrderItem::query()->where("order_id",$event->getOrder()->id)->pluck("product_id")->toArray();
self::updateProduct($productIdArr);
/*$renewIdArr = [];
$productUniArr = array_unique($productIdArr);
foreach ($productUniArr as $productId){
$renewIdArr[] = $productId;
if (50 == count($renewIdArr)){
self::updateProduct($renewIdArr);
$renewIdArr = [];
}
}
if (count($renewIdArr)){
self::updateProduct($renewIdArr);
}*/
}
public static function updateProduct($productIdArr)
{
//根据商品id直接groupby统计所有商品id的评分和评价数
$orderProductArr = OrderItem::query()
->whereIn('product_id', $productIdArr)
->whereNotNull('reviewed_at')
->whereHas('order', function ($query) {
$query->whereNotNull('paid_at');
})
->groupBy("product_id")
->select([
"product_id",
DB::raw('count(*) as review_count'),
DB::raw('avg(rating) as rating')
])->get()->toArray();
//拼接case when 批量更新
$reviewCountStr = "";
$ratingStr = "";
$endProductIdArr = [];
foreach ($orderProductArr as $orderProductOne){
$endProductIdArr[] = $orderProductOne["product_id"];
$reviewCountStr .= " when id = ".$orderProductOne["product_id"]." then " . $orderProductOne["review_count"];
$ratingStr .= " when id = ".$orderProductOne["product_id"]." then " . $orderProductOne["rating"];
}
ProductModel::whereIn('id', $endProductIdArr)
->update([
"review_count" => DB::raw("case ".$reviewCountStr." end"),
"rating" => DB::raw("case ".$ratingStr." end"),
]);
}
推荐文章: