优化 Laravel 查询

Question

我正在开展一个项目，需要以 Excel 格式导出产品销售报告。我正在使用Maatwebsite/Laravel-Excel我有三个模型。产品.phpclass products extends Model{    protected $table='products';    public $timestamps = false;    protected $fillable  = ['image', 'asin','price','strategy_id'];    public function orderDetails()    {        return $this->hasMany(order_details::class, 'SKU', 'asin');    }}订单.phpclass orders extends Model{    protected $table = 'orders';    public $timestamps = false;    protected $fillable = ['id','date','quantity','totalAmount'];    public function orderDetails()    {        return $this->hasMany(order_details::class);    }}订单详细信息.phpclass order_details extends Model{    protected $table = 'order_details';    protected $fillable = ['id','order_id','SKU','unitPrice','quantity','totalPrice'];    public function order()    {        return $this->belongsTo(orders::class);    }现在我想计算每个产品在过去 30 天、60 天和 90 天的销售次数。注意事项产品.asin = order_detils.SKUorder_detail 表没有订单日期列。一个订单可以有多个产品，且数量大于1。我当前的查询是：-$products = products::query();// Some where clauses/filters$products = $products->get();foreach($products as $product){    // Getting the order_details which has this product    $orderIds = order_details::where('SKU','=',$product->asin)->pluck('order_id');    $product->sales30days = orders::whereIn('id', $orderIds)->whereBetween('date', [Carbon::now()->subDays(30), Carbon::now()])->sum('quantity');    $product->sales60days = orders::whereIn('id', $orderIds)->whereBetween('date', [Carbon::now()->subDays(60), Carbon::now()])->sum('quantity');    $product->sales90days = orders::whereIn('id', $orderIds)->whereBetween('date', [Carbon::now()->subDays(90), Carbon::now()])->sum('quantity');    $product->sales120days = orders::whereIn('id', $orderIds)->whereBetween('date', [Carbon::now()->subDays(120), Carbon::now()])->sum('quantity');    $product->totalSold = orders::whereIn('id', $orderIds)->sum('quantity');}上面的查询给出了我需要的结果，但需要花费大量时间并且性能不友好。我有超过 100k 个产品。我有什么解决方案来优化这个查询吗？$products->paginate(100)我可以在 foreach 循环之后添加分页吗？

Answer 1

问题是您要进行很多查询，并且速度不可避免地会很慢。该解决方案应该具有更好的性能，因为您只进行了两次查询。

$orders = orders::with(['orderDetails'])->get();

$now = Carbon::now();

$quantities = [];

foreach($orders as $order) {

    $daysOld = $order->date->diffInDays($now);

    foreach ($order->orderDetails as $details) {

        if (!isset($quantities[$details->SKU])) {

            $quantities[$details->SKU]['30'] = 0;

            $quantities[$details->SKU]['60'] = 0;

            $quantities[$details->SKU]['90'] = 0;

            $quantities[$details->SKU]['120'] = 0;

            $quantities[$details->SKU]['total'] = 0;

        }

        if ($daysOld <= 30) {

            $quantities[$details->SKU]['30'] += $details->quantity;

        }

        if ($daysOld <= 60) {

            $quantities[$details->SKU]['60'] += $details->quantity;

        }

        

        if ($daysOld <= 90) {

            $quantities[$details->SKU]['90'] += $details->quantity;

        }

        if ($daysOld <= 120) {

            $quantities[$details->SKU]['120'] += $details->quantity;

        }

        $quantities[$details->SKU]['total'] += $details->quantity;

    }

}

return products::all()->map(function ($product) use ($quantities) {

    $product->sales30days  = $quantities[$product->asin]['30'];

    $product->sales60days  = $quantities[$product->asin]['60'];

    $product->sales90days  = $quantities[$product->asin]['90'];

    $product->sales120days = $quantities[$product->asin]['120'];

    $product->salesTotal   = $quantities[$product->asin]['total'];

    return $product;

});