在 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 salestaxtotalFROM ordersINNER JOIN ordered_items ON orders.id=ordered_items.order_idGROUP By ordered_items.supplierORDER 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 IlluminateSupportFacadesDB;use IlluminateSupportCollection; // 假设 $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 完整代码示例
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 开发中处理复杂报表和统计需求的重要技巧。
以上就是Laravel 多表聚合查询：高效整合关联数据与动态成本计算的详细内容，更多请关注php中文网其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1323651.html
微信扫一扫 
支付宝扫一扫 
