在 laravel 中，通过 query builder 结合 `join` 和 `db::raw`，可以高效地在一个查询中同时聚合来自主表和关联表的复杂数据，并灵活处理动态成本字段的求和。这种方法避免了多余的数据库往返，提高了数据检索的效率和代码的简洁性，特别适用于需要跨表统计并进行分组的场景。
场景概述与问题分析
在实际的业务场景中，我们经常需要从多个关联表中提取并聚合数据。例如，一个订单系统可能需要计算每个供应商的订单项总价、总数量，同时还需要统计与这些订单相关的总费用（如手续费、运费、销售税），甚至这些费用字段可能是动态指定的。
原始的解决方案可能包括：
首先查询 ordered_items 表，计算订单项的聚合数据（如 SUM(price)）。然后单独查询 orders 表，计算订单级别的聚合数据（如 SUM(fees)）。最后在应用层代码中尝试将这两部分数据合并。
这种分步查询的方法存在以下问题：
效率低下： 导致多次数据库往返，增加了查询延迟。逻辑复杂： 在应用层合并数据时，需要手动处理分组和关联逻辑，代码可读性和维护性较差。数据一致性： 难以保证在复杂分组条件下的数据一致性，容易出现逻辑错误。
为了解决这些问题，最佳实践是利用 SQL 的 JOIN 操作，在一个查询中完成所有必要的聚合。
数据库结构示例
我们以 ordered_items 和 orders 两张表为例：
ordered_items 表
id: 订单项IDorder_id: 关联到 orders 表的IDquantity: 数量price: 单价supplier: 供应商名称supplier_sku: 供应商SKU
orders 表
id: 订单IDfees: 手续费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 IlluminateSupportFacadesDB;use IlluminateSupportCollection;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],//
以上就是Laravel Query Builder：高效聚合关联表数据与动态成本计算的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1323499.html
微信扫一扫 
支付宝扫一扫 
