在 laravel 中，通过 query builder 结合 `join` 和 `db::raw`，可以高效地在一个查询中同时聚合来自主表和关联表的复杂数据，并灵活处理动态成本字段的求和。这种方法避免了多余的数据库往返，提高了数据检索的效率和代码的简洁性，特别适用于需要跨表统计并进行分组的场景。
场景概述与问题分析
在实际的业务场景中，我们经常需要从多个关联表中提取并聚合数据。例如，一个订单系统可能需要计算每个供应商的订单项总价、总数量，同时还需要统计与这些订单相关的总费用（如手续费、运费、销售税），甚至这些费用字段可能是动态指定的。
原始的解决方案可能包括：
- 首先查询 ordered_items 表，计算订单项的聚合数据（如 SUM(price)）。
- 然后单独查询 orders 表，计算订单级别的聚合数据（如 SUM(fees)）。
- 最后在应用层代码中尝试将这两部分数据合并。
这种分步查询的方法存在以下问题：
- 效率低下： 导致多次数据库往返，增加了查询延迟。
- 逻辑复杂： 在应用层合并数据时，需要手动处理分组和关联逻辑，代码可读性和维护性较差。
- 数据一致性： 难以保证在复杂分组条件下的数据一致性，容易出现逻辑错误。
为了解决这些问题，最佳实践是利用 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 聚合函数。
核心思路如下：
- 以 ordered_items 表作为基础，使用 join 方法将其与 orders 表通过 order_id 字段关联。
- 在 select 方法中，使用 DB::raw 来构建聚合表达式，包括来自 ordered_items 和 orders 表的字段。
- 使用 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],
// 
