Laravel中实现订单与发货单按站点和物料分组计算余额

开发者阿泽

2026-8-21

按站点和物料统计订单余额问题

数据库表结构

Orders 相关表

-- orders 表
+----+----------------+----------------+
| id | destination_id | requested_date |
+----+----------------+----------------+
|  1 |              1 | 2022-08-31     |
|  2 |              1 | 2022-09-01     |
+----+----------------+----------------+

-- order_items 表
+----------+-------------+------+
| order_id | material_id | qty  |
+----------+-------------+------+
|        1 |          39 | 1000 |
|        1 |           7 | 1500 |
|        1 |           4 |  250 |
|        2 |          48 | 5000 |
|        2 |          33 | 1500 |
+----------+-------------+------+

Shipments 相关表

-- shipments 表
+----+----------------+---------------+
| id | destination_id | shipment_date |
+----+----------------+---------------+
|  1 |              1 | 2022-08-27    |
+----+----------------+---------------+

-- shipment_items 表
+-------------+-------------+------+
| shipment_id | material_id | qty  |
+-------------+-------------+------+
|           1 |          39 |  900 |
|           1 |           4 |  450 |
|           1 |          12 | 2000 |
+-------------+-------------+------+

Materials 表

+----+------------------------------+
| id | name                         |
+----+------------------------------+
|  4 | Cengkeh                      |
|  7 | TJM Besuki Kasaran B 2021    |
| 12 | TJM Bondowoso Kasaran B 2021 |
| 33 | TJM Madura Lama A 2019       |
| 39 | TJM Paiton Kasaran 2019      |
| 48 | TJM Paiton Kasaran C 2020    |
+----+------------------------------+

Destinations 表

+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Malang-Pagelaran  |
|  2 | Tulungagung-Pakel |
|  3 | Malang-Araya      |
+----+-------------------+

需求说明

需要按日期分组，统计每个站点和物料的订单余额，预期输出格式如下：

[
    'destination' => '站点名称',
    'material' => '物料名称',
    'order' => '订单总数量',
    'shipment' => '发货总数量',
    'balance' => '发货数量 - 订单数量'
]

现有尝试代码

$from = Carbon::now()->subDays(30)->format('Y-m-d');
$to = Carbon::now()->format('Y-m-d');

$orders = \App\Models\MaterialOrder::select('materials.name as material','branches.name as destination','units.conversion','order.qty','requested_date')
    ->leftJoin('branches','destination_id','=','branches.id')
    ->leftJoin('material_order_items as order','material_orders.id','=','order.material_order_id')
    ->leftJoin('materials','materials.id','=','order.material_id')
    ->leftJoin('units','order.uom','=','units.name')
    ->where('material_orders.status',1)
    ->whereBetween('material_orders.created_at',[$from,$to])
    ->get()->toArray();

$shipments = \App\Models\MaterialShipment::select('materials.name as material','branches.name as destination','units.conversion','shipment.qty','shipment_date')
    ->leftJoin('branches','destination_id','=','branches.id')
    ->leftJoin('material_shipment_items as shipment','material_shipments.id','=','shipment.material_shipment_id')
    ->leftJoin('materials','materials.id','=','shipment.material_id')
    ->leftJoin('units','shipment.uom','=','units.name')
    ->where('material_shipments.status',1)
    ->whereBetween('material_shipments.created_at',[$from,$to])
    ->get()->toArray();

$collectedData = collect(array_unique(array_merge($orders,$shipments), SORT_REGULAR))->groupBy(['destination','material']);

当前问题

执行上述代码后，得到的结果未按预期合并订单和发货数量，而是将同一物料的订单、发货记录分开存储，示例结果如下：

[
    "TJM Paiton Kasaran 2019" => [
        0 => [
          "material" => "TJM Paiton Kasaran 2019"
          "destination" => "Malang-Pagelaran"
          "conversion" => 1
          "qty" => 1000
          "requested_date" => "2022-08-31"
        ],
        1 => [
          "material" => "TJM Paiton Kasaran 2019"
          "destination" => "Malang-Pagelaran"
          "conversion" => 1
          "qty" => 900
          "shipment_date" => "2022-08-27"
        ]
    ],
    // 其他物料记录省略
]

更新：订单和发货单的单独查询结果：

// Orders
array:5 [
  0 => array:5 [
    "material" => "TJM Paiton Kasaran 2019"
    "destination" => "Malang-Pagelaran"
    "conversion" => 1
    "qty" => 1000
    "requested_date" => "2022-08-31"
  ]
  1 => array:5 [
    "material" => "TJM Besuki Kasaran B 2021"
    "destination" => "Malang-Pagelaran"
    "conversion" => 1
    "qty" => 1500
    "requested_date" => "2022-08-31"
  ]
]

// Shipments
array:3 [
  0 => array:5 [
    "material" => "TJM Paiton Kasaran 2019"
    "destination" => "Malang-Pagelaran"
    "conversion" => 1
    "qty" => 900
    "shipment_date" => "2022-08-27"
  ]
  1 => array:5 [
    "material" => "Cengkeh"
    "destination" => "Malang-Pagelaran"
    "conversion" => 1
    "qty" => 450
    "shipment_date" => "2022-08-27"
  ]
  2 => array:5 [
    "material" => "TJM Bondowoso Kasaran B 2021"
    "destination" => "Malang-Pagelaran"
    "conversion" => 1
    "qty" => 2000
    "shipment_date" => "2022-08-27"
  ]
]

解决方案

方法一：集合聚合处理

先分别聚合订单和发货的总数量，再合并计算余额：

$from = Carbon::now()->subDays(30)->format('Y-m-d');
$to = Carbon::now()->format('Y-m-d');

// 聚合订单数据：按站点、物料分组求和
$orderAggregate = collect(\App\Models\MaterialOrder::select(
    'materials.name as material',
    'branches.name as destination',
    DB::raw('SUM(order.qty) as order_qty')
)
    ->leftJoin('branches', 'material_orders.destination_id', '=', 'branches.id')
    ->leftJoin('material_order_items as order', 'material_orders.id', '=', 'order.material_order_id')
    ->leftJoin('materials', 'materials.id', '=', 'order.material_id')
    ->where('material_orders.status', 1)
    ->whereBetween('material_orders.created_at', [$from, $to])
    ->groupBy('destination', 'material')
    ->get())->keyBy(function ($item) {
        return $item['destination'] . '|' . $item['material'];
    });

// 聚合发货数据：按站点、物料分组求和
$shipmentAggregate = collect(\App\Models\MaterialShipment::select(
    'materials.name as material',
    'branches.name as destination',
    DB::raw('SUM(shipment.qty) as shipment_qty')
)
    ->leftJoin('branches', 'material_shipments.destination_id', '=', 'branches.id')
    ->leftJoin('material_shipment_items as shipment', 'material_shipments.id', '=', 'shipment.material_shipment_id')
    ->leftJoin('materials', 'materials.id', '=', 'shipment.material_id')
    ->where('material_shipments.status', 1)
    ->whereBetween('material_shipments.created_at', [$from, $to])
    ->groupBy('destination', 'material')
    ->get())->keyBy(function ($item) {
        return $item['destination'] . '|' . $item['material'];
    });

// 合并所有唯一的站点-物料组合
$allKeys = array_unique(array_merge(array_keys($orderAggregate->all()), array_keys($shipmentAggregate->all())));

// 生成最终结果
$result = collect($allKeys)->map(function ($key) use ($orderAggregate, $shipmentAggregate) {
    list($destination, $material) = explode('|', $key);
    $orderQty = $orderAggregate->get($key)->order_qty ?? 0;
    $shipmentQty = $shipmentAggregate->get($key)->shipment_qty ?? 0;

    return [
        'destination' => $destination,
        'material' => $material,
        'order' => $orderQty,
        'shipment' => $shipmentQty,
        'balance' => $shipmentQty - $orderQty
    ];
});

// 若需按日期分组，可在聚合时加入日期字段（如DATE(material_orders.requested_date)）并添加到groupBy中

方法二：数据库联合查询聚合

通过UNION ALL合并订单和发货数据，再二次聚合计算：

$from = Carbon::now()->subDays(30)->format('Y-m-d');
$to = Carbon::now()->format('Y-m-d');

// 订单数据查询（含日期分组）
$orderQuery = \App\Models\MaterialOrder::select(
    'materials.name as material',
    'branches.name as destination',
    DB::raw('SUM(order.qty) as order_qty'),
    DB::raw('0 as shipment_qty'),
    DB::raw('DATE(material_orders.requested_date) as date')
)
    ->leftJoin('branches', 'material_orders.destination_id', '=', 'branches.id')
    ->leftJoin('material_order_items as order', 'material_orders.id', '=', 'order.material_order_id')
    ->leftJoin('materials', 'materials.id', '=', 'order.material_id')
    ->where('material_orders.status', 1)
    ->whereBetween('material_orders.created_at', [$from, $to])
    ->groupBy('date', 'destination', 'material');

// 发货数据查询（含日期分组）
$shipmentQuery = \App\Models\MaterialShipment::select(
    'materials.name as material',
    'branches.name as destination',
    DB::raw('0 as order_qty'),
    DB::raw('SUM(shipment.qty) as shipment_qty'),
    DB::raw('DATE(material_shipments.shipment_date) as date')
)
    ->leftJoin('branches', 'material_shipments.destination_id', '=', 'branches.id')
    ->leftJoin('material_shipment_items as shipment', 'material_shipments.id', '=', 'shipment.material_shipment_id')
    ->leftJoin('materials', 'materials.id', '=', 'shipment.material_id')
    ->where('material_shipments.status', 1)
    ->whereBetween('material_shipments.created_at', [$from, $to])
    ->groupBy('date', 'destination', 'material');

// 合并查询并最终聚合
$result = DB::table(DB::raw("({$orderQuery->unionAll($shipmentQuery)->toSql()}) as combined"))
    ->mergeBindings($orderQuery)
    ->mergeBindings($shipmentQuery)
    ->select(
        'date',
        'destination',
        'material',
        DB::raw('SUM(order_qty) as order'),
        DB::raw('SUM(shipment_qty) as shipment'),
        DB::raw('SUM(shipment_qty) - SUM(order_qty) as balance')
    )
    ->groupBy('date', 'destination', 'material')
    ->get();

两种方法各有适用场景：方法一适合数据量较小的场景，在内存中完成聚合；方法二通过数据库层处理，性能更优，适合大数据量场景。

内容的提问来源于stack exchange，提问作者owf

Laravel中实现订单与发货单按站点和物料分组计算余额

数据库表结构 #

Orders 相关表 #

Shipments 相关表 #

Materials 表 #

Destinations 表 #

需求说明 #

现有尝试代码 #

当前问题 #

解决方案 #

方法一：集合聚合处理 #

方法二：数据库联合查询聚合 #