在 Laravel Eloquent 中实现带 SUM 函数的分组聚合查询

本文详细介绍了如何在 Laravel Eloquent 中将包含 SUM 聚合函数和 GROUP BY 子句的复杂原始 SQL 查询转换为更优雅、可维护的 Eloquent 语法。核心在于利用 DB::raw() 方法将聚合表达式嵌入到 select 语句中，从而实现对分组数据的精确统计，同时获取其他相关字段。文章将通过示例代码和注意事项，指导开发者正确构建此类查询。

1. 理解原始 SQL 查询的需求

在处理数据库操作时，我们经常需要执行聚合查询，例如计算某个字段的总和，并根据另一个字段进行分组。以下是一个典型的原始 SQL 查询示例，它计算每个产品在特定用户购物车中的总数量：

SELECT
    SUM(carts.quantity) as countq,
    carts.productid,
    products.name,
    products.price,
    products.gallary
FROM
    carts, products
WHERE
    carts.productid = products.id
    AND carts.userid = [userid]
GROUP BY
    carts.productid
ORDER BY
    carts.productid;

这个查询的核心在于：

• SUM(carts.quantity) as countq: 计算 carts 表中 quantity 字段的总和，并将其命名为 countq。
• FROM carts, products / WHERE carts.productid = products.id: 执行 carts 表和 products 表的连接。
• WHERE carts.userid = [userid]: 根据用户ID过滤购物车记录。
• GROUP BY carts.productid: 按照 productid 对结果进行分组，使得 SUM 函数针对每个产品ID独立计算。
• ORDER BY carts.productid: 按照 productid 对最终结果进行排序。

2. Laravel Eloquent 聚合查询的挑战

当尝试将上述原始 SQL 转换为 Laravel Eloquent 语法时，开发者可能会遇到一些挑战。例如，直接在 groupBy 之后使用 sum() 方法通常无法达到预期效果，因为它通常用于获取单一的总和值，而不是在分组查询中同时选择聚合值和其他分组列。

错误的尝试示例：

use Illuminate\Support\Facades\DB;

$userid = 1; // 示例用户ID

$results = DB::table('carts')
    ->join('products','carts.productid','products.id')
    ->select('carts.quantity','carts.productid', 'products.name', 'products.price','products.gallary')
    ->where('carts.userid',$userid)
    ->groupBy('carts.productid')
    ->orderBy('carts.productid')
    ->sum('carts.quantity') // 此处调用 sum() 会导致问题，因为它会返回一个单一的总和，而不是分组结果
    ->get(); // get() 将永远不会被执行

上述代码的问题在于 -youjiankuohaophpcnsum('carts.quantity') 会立即执行查询并返回一个数字（所有购物车商品的总数量），而不是返回一个包含分组数据的集合。因此，后续的 ->get() 方法将永远不会被调用。

网龙b2b仿阿里巴巴电子商务平台

本系统经过多次升级改造，系统内核经过多次优化组合，已经具备相对比较方便快捷的个性化定制的特性，用户部署完毕以后，按照自己的运营要求，可实现快速定制会费管理，支持在线缴费和退费功能财富中心，管理会员的诚信度数据单客户多用户登录管理全部信息支持审批和排名不同的会员级别有不同的信息发布权限企业站单独生成，企业自主决定更新企业站信息留言、询价、报价统一管理，分系统查看分类信息参数化管理，支持多样分类信息，

0

查看详情

3. 正确实现方式：结合 DB::raw()

要正确地在 Laravel Eloquent 中实现带 SUM 函数的分组聚合查询，我们需要利用 DB::raw() 方法将原始 SQL 聚合表达式嵌入到 select 语句中。这样，数据库将处理聚合逻辑，而 Eloquent 则负责构建查询的其余部分。

以下是实现上述原始 SQL 查询的正确 Eloquent 方式：

<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB; // 引入 DB Facade

class CartController extends Controller
{
    public function getUserCartSummary($userid)
    {
        // 确保 $userid 是一个安全的整数或已通过验证
        // 例如：$userid = auth()->id(); 或 $userid = (int)$request->input('userid');

        $cartSummary = DB::table('carts')
            // 1. 执行表连接
            ->join('products', 'carts.productid', '=', 'products.id')
            // 2. 选择需要的列，包括使用 DB::raw() 定义的 SUM 聚合函数
            ->select(
                DB::raw('SUM(carts.quantity) as countq'), // 聚合函数及其别名
                'carts.productid',
                'products.name',
                'products.price',
                'products.gallary'
            )
            // 3. 添加过滤条件
            ->where('carts.userid', $userid)
            // 4. 定义分组字段
            ->groupBy('carts.productid')
            // 5. 定义排序规则
            ->orderBy('carts.productid', 'asc')
            // 6. 执行查询并获取结果集
            ->get();

        // 返回或处理查询结果
        return response()->json($cartSummary);
    }
}

代码解析：

• DB::table('carts'): 指定查询的主表为 carts。
• ->join('products', 'carts.productid', '=', 'products.id'): 执行 carts 表和 products 表的内连接，连接条件是 carts.productid 等于 products.id。
• ->select(DB::raw('SUM(carts.quantity) as countq'), ...): 这是关键部分。
  • DB::raw('SUM(carts.quantity) as countq')：告诉 Laravel 将 SUM(carts.quantity) as countq 作为原始 SQL 表达式直接插入到 SELECT 语句中。这样，数据库会计算每个分组的 quantity 总和，并将其作为 countq 返回。
  • 除了聚合结果，我们还列出了其他需要随分组一起返回的字段，如 carts.productid, products.name 等。
• ->where('carts.userid', $userid): 添加 userid 过滤条件。
• ->groupBy('carts.productid'): 按照 carts.productid 对结果进行分组。
• ->orderBy('carts.productid', 'asc'): 按照 carts.productid 升序排序。
• ->get(): 执行构建好的查询并返回一个 Illuminate\Support\Collection 实例，其中包含每个分组的聚合结果和其他选定字段。

4. 注意事项

• DB::raw() 的安全性: DB::raw() 允许您插入任何原始 SQL 表达式。虽然它非常强大，但请务必注意 SQL 注入风险。如果 DB::raw() 中包含用户输入，请确保已对输入进行严格的验证和清理。在本例中，SUM(carts.quantity) as countq 是硬编码的，不涉及用户输入，因此是安全的。而 $userid 变量则应在传入查询前进行安全处理（例如，通过 Eloquent 的 where 方法会自动转义）。
• 聚合函数与 select 语句: 当您需要使用 GROUP BY 子句，并且希望在结果中同时包含聚合值（如 SUM、COUNT、AVG 等）以及其他非聚合列时，这些聚合函数必须作为 select 语句的一部分出现，通常通过 DB::raw() 来实现。
• 避免冗余条件: 在原始 SQL 解决方案中，如果 join 语句已经定义了连接条件（例如 carts.productid = products.id），那么在 where 子句中重复相同的条件 where('carts.productid','products.id') 通常是冗余的，除非有特定的业务逻辑需要。在上述 Eloquent 示例中，我们移除了这个冗余的 where 条件，因为 join 已经处理了表的关联。
• sum() 方法的正确用法: Eloquent 的 ->sum('column') 方法主要用于获取一个单一的、未经分组的总和。例如，DB::table('carts')->where('userid', $userid)->sum('quantity') 会返回该用户所有购物车商品的总数量。它不适用于需要同时获取分组聚合值和其他列的场景。

总结

通过巧妙地运用 DB::raw() 方法，Laravel Eloquent 能够优雅地处理复杂的 SQL 聚合查询，包括那些带有 SUM 函数和 GROUP BY 子句的场景。理解 DB::raw() 的作用以及它与 select 语句的结合方式，是构建高效且可维护的 Laravel 数据库查询的关键。在实际开发中，始终优先使用 Eloquent 提供的简洁方法，但在遇到复杂情况时，DB::raw() 提供了一个强大的逃生舱口，让您能够无缝地集成原始 SQL 逻辑。

以上就是在 Laravel Eloquent 中实现带 SUM 函数的分组聚合查询的详细内容，更多请关注php中文网其它相关文章！