Laravel Query Builder：高效聚合关联表数据与动态成本计算

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;

class OrderAnalyticsService
{
    /**
     * 获取按供应商聚合的订单数据，包括订单项和订单级别的成本。
     *
     * @param Collection $ordersCollection 包含订单ID的集合，例如 Order 模型的集合。
     * @param array $suppliers 供应商名称数组。
     * @param array $dynamicCosts 动态成本字段名称数组，例如 ['fees', 'shipping_cost']。
     * @return Collection 聚合后的数据。
     */
    public function getAggregatedOrderData(Collection $ordersCollection, array $suppliers, array $dynamicCosts): Collection
    {
        // 提取订单ID数组
        $orderIds = $ordersCollection->pluck('id')->toArray();

        // 动态构建成本字段的求和表达式
        // 例如：SUM(orders.fees::numeric) + SUM(orders.shipping_cost::numeric)
        $costSelects = [];
        foreach ($dynamicCosts as $costField) {
            // 确保对 money 类型进行数值转换，并明确指定表名，以避免类型转换错误
            $costSelects[] = "SUM(orders.{$costField}::numeric)";
        }
        $dynamicCostExpression = count($costSelects) > 0 ? implode(' + ', $costSelects) : '0';

        $data = DB::table('ordered_items')
            // 使用 INNER JOIN 连接 orders 表
            ->join('orders', 'orders.id', '=', 'ordered_items.order_id')
            // 过滤指定的订单ID
            ->whereIn('ordered_items.order_id', $orderIds)
            // 过滤指定的供应商
            ->whereIn('ordered_items.supplier', $suppliers)
            ->select([
                // 按照 SQL 示例分组，选择供应商名称
                'ordered_items.supplier',
                // 聚合 ordered_items 表的字段
                DB::raw('SUM(ordered_items.price::numeric) as cogs'),
                DB::raw('SUM(ordered_items.quantity) as qty'),
                // 计算平均价格，注意分母为0的情况，但此处简化处理
                DB::raw('(SUM(ordered_items.price::numeric) / SUM(ordered_items.quantity)) as avg_price_per_qty'),
                // 聚合 orders 表的固定费用字段，注意明确表名和类型转换
                DB::raw("SUM(orders.fees::numeric) as feestotal"),
                DB::raw("SUM(orders.shipping_cost::numeric) as shippingtotal"),
                DB::raw("SUM(orders.sales_tax::numeric) as salestaxtotal"),
                // 动态成本字段的总和
                DB::raw("({$dynamicCostExpression}) as dynamic_costs_total")
            ])
            // 按照 ordered_items.supplier 进行分组，与提供的 SQL 示例一致
            ->groupBy('ordered_items.supplier')
            ->get();

        return $data;
    }
}

// 示例用法：
// $orders = collect([
//     (object)['id' => 1],
//