优化 Laravel 查询

Question

我正在开展一个项目，需要以 Excel 格式导出产品销售报告。我正在使用Maatwebsite/Laravel-Excel

我有三个模型。

产品.php

class 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');

    }

}

订单.php

class orders extends Model

{

    protected $table = 'orders';

    public $timestamps = false;

    protected $fillable = ['id','date','quantity','totalAmount'];

    public function orderDetails()

    {

        return $this->hasMany(order_details::class);

    }

}

订单详细信息.php

class 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 天的销售次数。

注意事项

1. 产品.asin = order_detils.SKU

2. order_detail 表没有订单日期列。

3. 一个订单可以有多个产品，且数量大于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 个产品。

1. 我有什么解决方案来优化这个查询吗？

2. $products->paginate(100)我可以在 foreach 循环之后添加分页吗？

Answer 1

问题是您要进行很多查询，并且速度不可避免地会很慢。该解决方案应该具有更好的性能，因为您只进行了两次查询。$orders = orders::with(['orderDetails'])->get();$now = Carbon::now();$quantities = [];foreach($orders as $order) {&nbsp; &nbsp; $daysOld = $order->date->diffInDays($now);&nbsp; &nbsp; foreach ($order->orderDetails as $details) {&nbsp; &nbsp; &nbsp; &nbsp; if (!isset($quantities[$details->SKU])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['30'] = 0;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['60'] = 0;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['90'] = 0;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['120'] = 0;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['total'] = 0;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if ($daysOld <= 30) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['30'] += $details->quantity;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if ($daysOld <= 60) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['60'] += $details->quantity;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; if ($daysOld <= 90) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['90'] += $details->quantity;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if ($daysOld <= 120) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['120'] += $details->quantity;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; $quantities[$details->SKU]['total'] += $details->quantity;&nbsp; &nbsp; }}return products::all()->map(function ($product) use ($quantities) {&nbsp; &nbsp; $product->sales30days&nbsp; = $quantities[$product->asin]['30'];&nbsp; &nbsp; $product->sales60days&nbsp; = $quantities[$product->asin]['60'];&nbsp; &nbsp; $product->sales90days&nbsp; = $quantities[$product->asin]['90'];&nbsp; &nbsp; $product->sales120days = $quantities[$product->asin]['120'];&nbsp; &nbsp; $product->salesTotal&nbsp; &nbsp;= $quantities[$product->asin]['total'];&nbsp; &nbsp; return $product;});&nbsp;