在 Laravel 应用开发中，我们经常会遇到需要从多个关联表中聚合数据的情况。例如，在一个订单管理系统中，我们可能需要计算每个供应商的商品总成本、总数量，同时还要汇总该供应商所有订单产生的运费、手续费等订单级别的成本。传统的做法可能是执行多次数据库查询，然后将结果在应用层进行合并，但这往往效率低下且代码复杂。本文将详细介绍如何利用 Laravel 的查询构建器，通过一次高效的数据库查询来解决这类多表聚合问题，并特别关注如何处理动态指定的成本字段。
1. 数据模型概览
为了更好地理解问题，我们首先审视涉及到的两个核心数据表结构：ordered_items 和 orders。
ordered_items 表:
此表存储了订单中每个商品的详细信息。
+----------------------+--------------------------------+-------------------------------------------------------------+
| Column | Type | Modifiers |
|----------------------+--------------------------------+-------------------------------------------------------------|
| id | integer | not null default nextval('ordered_items_id_seq'::regclass) |
| order_id | integer | not null |
| quantity | integer | not null default 1 |
| name | text | |
| price | money | |
| supplier | character varying(255) | |
| supplier_sku | character varying(255) | |
| ... | ... | ... |
+----------------------+--------------------------------+-------------------------------------------------------------+ordered_items.order_id 列是外键，关联到 orders 表的 id 列。
orders 表:
此表存储了订单的整体信息，包括一些订单级别的成本。
+-------------------------------------+--------------------------------+------------------------------------------------------+
| Column | Type | Modifiers |
|-------------------------------------+--------------------------------+------------------------------------------------------|
| id | integer | not null default nextval('orders_id_seq'::regclass) |
| created_at | timestamp(0) without time zone | |
| shipping_cost | money | |
| sales_tax | money | |
| fees | money | |
| ... | ... | ... |
+-------------------------------------+--------------------------------+------------------------------------------------------+2. 原始方法的局限性分析
在实际开发中，一种常见的初级方法是分别查询这两个表，然后在 PHP 代码中手动合并和计算。例如，用户最初尝试的方案：
-
查询 ordered_items 以获取商品层面的聚合数据：
$data = DB::table('ordered_items') ->whereIn('order_id', $orders) ->whereIn('supplier', $suppliers) ->select([ 'supplier_sku', DB::raw('SUM(price) as cogs'), DB::raw('SUM(quantity) as qty'), DB::raw('(SUM(price::numeric) / SUM(quantity)) as avg') ]) ->groupBy('supplier_sku') ->get(); -
单独查询 orders 以获取订单层面的成本数据：
// $costs 可能是 ['fees', 'shipping_cost'] $concatCosts = ''; if (count($costs) > 0) { $concatCosts = array_reduce($costs, function ($carry, $item) { return $carry . ($carry ? '+' : '') . $item; }); } $orderCosts = ''; if (count($costs)) { $orderCosts = DB::table('orders') ->whereIn('id', $orders) ->select( 'id', DB::raw('sum (' . $concatCosts . ') as costs') ) ->groupBy('id') ->get(); }这种方法存在明显缺陷：
- 效率低下： 至少需要两次独立的数据库查询，增加了数据库往返开销。
- 数据合并复杂： 在 PHP 层面手动将 ordered_items 的聚合结果与 orders 的聚合结果进行匹配和合并，逻辑繁琐且容易出错。
- 难以维护： 当需求变化（例如需要更多聚合字段或不同的分组方式）时，修改逻辑会变得复杂。
3. SQL 联合查询方案解析
解决上述问题的标准 SQL 方法是使用 INNER JOIN 将两个表连接起来，然后通过 GROUP BY 进行聚合。例如，以下 SQL 查询可以正确地按供应商汇总订单费用、运费和税费：
SELECT ordered_items.supplier, sum(orders.fees) as feestotal, sum(orders.shipping_cost) as shippingtotal, sum(orders.sales_tax) as salestaxtotal FROM orders INNER JOIN ordered_items ON orders.id=ordered_items.order_id GROUP By ordered_items.supplier ORDER BY ordered_items.supplier;
这个 SQL 查询的关键在于：
- INNER JOIN ordered_items ON orders.id=ordered_items.order_id：根据 order_id 将 orders 表和 ordered_items 表关联起来。
- GROUP BY ordered_items.supplier：按照 ordered_items 表中的 supplier 字段进行分组，使得所有聚合函数（SUM）都在每个供应商的范围内进行计算。
4. Laravel Query Builder 实现单次高效查询
现在，我们将上述 SQL 逻辑转换为 Laravel 的查询构建器代码，实现一次性查询所有所需数据。
4.1 核心思路
利用 Laravel 的 DB::table() 方法启动查询构建器，通过 join() 方法将 orders 表与 ordered_items 表关联起来，并在 select() 语句中同时聚合两表的字段。
4.2 构建查询步骤
假设我们有一个 $orders 集合（包含订单 ID）和一个 $suppliers 数组（包含供应商名称），以及一个 $costs 数组（例如 ['fees', 'shipping_cost', 'sales_tax']，表示需要动态求和的订单级成本字段）。
-
选择主表并进行关联： 我们以 ordered_items 作为起点，并 INNER JOIN 到 orders 表。
use Illuminate\Support\Facades\DB; use Illuminate\Support\Collection; // 假设 $orders 是 Collection 类型 public function getAggregatedOrderData(Collection $orders, array $suppliers, array $costs): array { $query = DB::table('ordered_items') ->join('orders', 'orders.id', '=', 'ordered_items.order_id'); -
定义聚合选择列： 同时选择 ordered_items 表的聚合字段和 orders 表的聚合字段。注意，为了避免字段名冲突，最好明确指定表名。
$selects = [ 'ordered_items.supplier', // 按供应商分组，所以选择供应商字段 DB::raw('SUM(ordered_items.price) as cogs'), DB::raw('SUM(ordered_items.quantity) as qty'), DB::raw('(SUM(ordered_items.price::numeric) / NULLIF(SUM(ordered_items.quantity), 0)) as avg_price_per_unit'), // 避免除以零 ];这里使用了 NULLIF(SUM(ordered_items.quantity), 0) 来处理 quantity 总和为零的情况，避免除以零的错误。
-
处理动态成本字段： 根据 $costs 数组动态构建 orders 表中成本字段的求和表达式。
if (!empty($costs)) { // 构建动态求和字符串，例如 "SUM(orders.fees) + SUM(orders.shipping_cost)" $costSumExpressions = collect($costs) ->map(fn($costField) => "SUM(orders.{$costField})") ->implode(' + '); if ($costSumExpressions) { $selects[] = DB::raw("({$costSumExpressions}) as combined_costs"); } }这段代码将 $costs 数组中的每个成本字段转换为 SUM(orders.field_name) 形式，然后用 + 连接起来，形成一个完整的 DB::raw 表达式。
-
应用筛选条件和分组： 应用 whereIn 条件过滤订单和供应商，并按照 ordered_items.supplier 进行分组。
$data = $query->select($selects) ->whereIn('ordered_items.order_id', $orders->pluck('id')->toArray()) // 确保 $orders 是一个包含订单ID的集合或数组 ->whereIn('ordered_items.supplier', $suppliers) ->groupBy('ordered_items.supplier') ->get(); return $data->toArray(); }注意：如果 $orders 是一个 Collection 对象，你需要使用 pluck('id')->toArray() 来获取其包含的 ID 数组。
4.3 完整代码示例
<?php
namespace App\Services;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;
class OrderAggregationService
{
/**
* 获取按供应商聚合的订单商品及订单成本数据。
*
* @param Collection $orders 包含订单ID的集合
* @param array $suppliers 供应商名称数组
* @param array $costs 需要动态求和的订单成本字段数组 (e.g., ['fees', 'shipping_cost', 'sales_tax'])
* @return array
*/
public function getAggregatedOrderData(Collection $orders, array $suppliers, array $costs = []): array
{
// 确保 $orders 集合不为空，且包含 id 字段
if ($orders->isEmpty()) {
return [];
}
$query = DB::table('ordered_items')
->join('orders', 'orders.id', '=', 'ordered_items.order_id');
$selects = [
'ordered_items.supplier',
DB::raw('SUM(ordered_items.price) as cogs'),
DB::raw('SUM(ordered_items.quantity) as qty'),
// 使用 NULLIF 避免除以零错误，并进行类型转换以确保精确计算
DB::raw('(SUM(ordered_items.price::numeric) / NULLIF(SUM(ordered_items.quantity), 0)) as avg_price_per_unit'),
];
// 动态添加订单成本的聚合
if (!empty($costs)) {
$costSumExpressions = collect($costs)
->map(fn($costField) => "SUM(orders.{$costField}::numeric)") // 同样进行类型转换
->implode(' + ');
if ($costSumExpressions) {
$selects[] = DB::raw("({$costSumExpressions}) as combined_costs");
}
}
$result = $query->select($selects)
->whereIn('ordered_items.order_id', $orders->pluck('id')->toArray())
->whereIn('ordered_items.supplier', $suppliers)
->groupBy('ordered_items.supplier')
->get();
return $result->toArray();
}
}5. 注意事项与最佳实践
- 字段歧义处理： 当两个关联表存在同名字段时（例如 id，或者 fees 如果 ordered_items 中也有同名字段），在 select 或 where 子句中务必使用 table_name.column_name 的形式明确指定字段，以避免歧义。
- DB::raw 的使用： DB::raw() 允许你直接插入原生 SQL 表达式。它在处理复杂聚合、函数调用或动态构建 SQL 片段时非常有用。然而，使用 DB::raw() 时需要注意潜在的 SQL 注入风险。在本例中，$costs 数组中的字段名通常是应用内部预定义的，风险较低。但如果 $costs 数组来自用户输入，务必进行严格的白名单验证。
- 数据类型转换 (::numeric)： 在 PostgreSQL 中，money 类型在进行数学运算（特别是除法）时可能需要显式转换为 numeric 类型，以确保计算的准确性。示例代码中已在 avg_price_per_unit 和 combined_costs 的计算中进行了演示。
- 性能考量： 联合查询通常比多次独立查询更高效，因为它减少了数据库往返次数。然而，对于非常大的数据集，仍然需要确保 order_id、supplier 等用于 JOIN 和 WHERE 子句的列上存在合适的数据库索引，以优化查询性能。
- Eloquent ORM 与 Query Builder： 对于这种复杂的聚合查询，Laravel 的 Query Builder (即 DB::table()) 通常比 Eloquent ORM 更灵活和直接。虽然 Eloquent 提供了关系方法，但在需要深度聚合和自定义 GROUP BY 逻辑时，Query Builder 往往是更好的选择。
6. 总结
通过本文的讲解，我们学习了如何在 Laravel 中利用查询构建器，结合 JOIN 和 GROUP BY 语句，高效地从多个关联表中聚合数据。特别是，我们掌握了如何动态构建 DB::raw 表达式来汇总指定成本字段，从而极大地提高了查询的灵活性和代码的复用性。这种方法不仅优化了数据库交互，减少了查询次数，还使得数据处理逻辑更加清晰和易于维护，是 Laravel 开发中处理复杂报表和统计需求的重要技巧。
