Laravel聚合关联数据与动态成本计算方法

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习文章相关编程知识。下面本篇文章就来带大家聊聊《Laravel查询构建器：聚合关联数据与动态成本计算》，介绍一下，希望对大家的知识积累有所帮助，助力实战开发！

在 Laravel 中，通过 Query Builder 结合 `JOIN` 和 `DB::raw`，可以高效地在一个查询中同时聚合来自主表和关联表的复杂数据，并灵活处理动态成本字段的求和。这种方法避免了多余的数据库往返，提高了数据检索的效率和代码的简洁性，特别适用于需要跨表统计并进行分组的场景。

场景概述与问题分析

在实际的业务场景中，我们经常需要从多个关联表中提取并聚合数据。例如，一个订单系统可能需要计算每个供应商的订单项总价、总数量，同时还需要统计与这些订单相关的总费用（如手续费、运费、销售税），甚至这些费用字段可能是动态指定的。

原始的解决方案可能包括：

1. 首先查询 ordered_items 表，计算订单项的聚合数据（如 SUM(price)）。
2. 然后单独查询 orders 表，计算订单级别的聚合数据（如 SUM(fees)）。
3. 最后在应用层代码中尝试将这两部分数据合并。

这种分步查询的方法存在以下问题：

• 效率低下： 导致多次数据库往返，增加了查询延迟。
• 逻辑复杂： 在应用层合并数据时，需要手动处理分组和关联逻辑，代码可读性和维护性较差。
• 数据一致性： 难以保证在复杂分组条件下的数据一致性，容易出现逻辑错误。

为了解决这些问题，最佳实践是利用 SQL 的 JOIN 操作，在一个查询中完成所有必要的聚合。

数据库结构示例

我们以 ordered_items 和 orders 两张表为例：

ordered_items 表

• id: 订单项ID
• order_id: 关联到 orders 表的ID
• quantity: 数量
• price: 单价
• supplier: 供应商名称
• supplier_sku: 供应商SKU

orders 表

• id: 订单ID
• fees: 手续费
• shipping_cost: 运费
• sales_tax: 销售税

我们的目标是：根据 ordered_items.supplier 进行分组，计算每个供应商的订单项总价 (cogs)、总数量 (qty)、平均价格 (avg)，并同时统计该供应商所有订单的总手续费 (feestotal)、总运费 (shippingtotal)、总销售税 (salestaxtotal)，以及一个可变成本字段集合的总和。

解决方案：使用 JOIN 和 DB::raw 进行单次查询

Laravel 的 Query Builder 提供了 join 方法来连接表，并通过 select 和 DB::raw 方法来执行复杂的 SQL 聚合函数。

核心思路如下：

1. 以 ordered_items 表作为基础，使用 join 方法将其与 orders 表通过 order_id 字段关联。
2. 在 select 方法中，使用 DB::raw 来构建聚合表达式，包括来自 ordered_items 和 orders 表的字段。
3. 使用 groupBy 方法指定分组字段。

动态成本字段的处理

一个常见的需求是，需要根据用户输入或其他业务逻辑，动态地选择要聚合的成本字段（例如，有时只计算 fees，有时计算 fees 和 shipping_cost 的总和）。这可以通过预先构建 DB::raw 表达式来实现。

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],
//

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~