登录
首页 >  文章 >  php教程

Laravel多表查询：高效整合数据与成本计算

时间：2025-10-30 10:54:36 303浏览 收藏

有志者，事竟成！如果你在学习文章，那么本文《Laravel多表查询：高效整合数据与动态计算成本》，就很适合你！文章讲解的知识点主要包括，若是你对本文感兴趣，或者是想搞懂其中某个知识点，就请你继续往下看吧~

Laravel 多表聚合查询：高效整合关联数据与动态成本计算

在 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 代码中手动合并和计算。例如，用户最初尝试的方案：

  1. 查询 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();

  2. 单独查询 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']，表示需要动态求和的订单级成本字段）。

  1. 选择主表并进行关联： 我们以 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');

  2. 定义聚合选择列： 同时选择 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 总和为零的情况，避免除以零的错误。

  3. 处理动态成本字段： 根据 $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 表达式。

  4. 应用筛选条件和分组： 应用 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. 注意事项与最佳实践

  1. 字段歧义处理： 当两个关联表存在同名字段时（例如 id，或者 fees 如果 ordered_items 中也有同名字段），在 select 或 where 子句中务必使用 table_name.column_name 的形式明确指定字段，以避免歧义。
  2. DB::raw 的使用： DB::raw() 允许你直接插入原生 SQL 表达式。它在处理复杂聚合、函数调用或动态构建 SQL 片段时非常有用。然而，使用 DB::raw() 时需要注意潜在的 SQL 注入风险。在本例中，$costs 数组中的字段名通常是应用内部预定义的，风险较低。但如果 $costs 数组来自用户输入，务必进行严格的白名单验证。
  3. 数据类型转换 (::numeric)： 在 PostgreSQL 中，money 类型在进行数学运算（特别是除法）时可能需要显式转换为 numeric 类型，以确保计算的准确性。示例代码中已在 avg_price_per_unit 和 combined_costs 的计算中进行了演示。
  4. 性能考量： 联合查询通常比多次独立查询更高效，因为它减少了数据库往返次数。然而，对于非常大的数据集，仍然需要确保 order_id、supplier 等用于 JOIN 和 WHERE 子句的列上存在合适的数据库索引，以优化查询性能。
  5. 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多表查询：高效整合数据与成本计算》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多文章知识！

相关阅读
更多>
最新阅读
更多>
课程推荐
更多>