本文旨在指导开发者如何在laravel query builder中正确实现包含`sum`聚合函数和`join`操作的复杂查询。通过分析原生sql及其在laravel中的常见转换误区，我们将详细阐述如何利用`db::raw()`方法在`select`语句中集成聚合函数，同时结合`join`、`where`、`groupby`和`orderby`等方法，构建出高效且符合laravel风格的查询语句，确保聚合结果与相关字段一同返回。
在开发Web应用时，我们经常需要执行涉及多个表的复杂数据库查询，其中聚合函数（如SUM、COUNT、AVG）和表连接（JOIN）是常见的组合。Laravel的Query Builder提供了一套流畅的API来构建这些查询，但对于聚合函数与多列选择的结合，初学者可能会遇到一些挑战。本文将详细讲解如何将包含SUM聚合函数的原生SQL查询正确转换为Laravel Query Builder的语法。
问题场景分析
假设我们有一个购物车系统，需要查询特定用户购物车中每个产品的总数量，并显示产品名称、价格等信息。原生SQL查询可能如下所示：
SELECT SUM(carts.quantity) as couuntq, carts.productid, products.name, products.price, products.gallary FROM carts, products WHERE carts.productid = products.id AND carts.userid = [用户ID] GROUP BY carts.productid ORDER BY carts.productid;
当尝试将此类查询直接转换为Laravel Query Builder时，常见的错误是试图在select多个字段后，直接调用sum()方法：
// 错误的尝试
DB::table('carts')
->join('products','carts.productid','products.id')
->select('carts.quantity','carts.productid', 'products.name', 'products.name', 'products.price','products.gallary')
->where('carts.userid',$userid)
->groupBy('carts.productid')
->orderBy('carts.productid')
->sum('carts.quantity') // 这里的sum()是一个终止方法，它会直接返回总和，而不是作为select的一部分
->get(); // 在sum()之后调用get()会报错这种错误的原因在于，Query Builder的sum()、count()等聚合方法通常是“终止”方法，它们会直接执行查询并返回聚合结果的单一值，而不是将聚合表达式添加到SELECT子句中以与其他列一起返回。如果需要将聚合函数作为SELECT子句的一部分，以便与分组后的其他列一同返回，我们需要使用DB::raw()。
正确的Laravel Query Builder实现
要正确实现上述查询，我们需要利用DB::raw()方法将SUM函数表达式直接插入到select语句中。
use Illuminate\Support\Facades\DB;
// 假设 $userid 已经定义
$userid = 1; // 示例用户ID
$results = DB::table('carts')
->join('products', 'carts.productid', '=', 'products.id')
->select(
DB::raw('SUM(carts.quantity) as couuntq'), // 使用 DB::raw() 包含 SUM 聚合函数
'carts.productid',
'products.name',
'products.price',
'products.gallary'
)
->where('carts.userid', $userid) // 筛选特定用户
->groupBy('carts.productid') // 根据产品ID分组
->orderBy('carts.productid', 'asc') // 按照产品ID升序排序
->get();
// 打印结果
foreach ($results as $item) {
echo "产品ID: " . $item->productid . ", 名称: " . $item->name . ", 总数量: " . $item->couuntq . ", 价格: " . $item->price . "\n";
}代码解析与关键点
-
DB::table('carts'):
- 指定查询的主表为carts。
-
->join('products', 'carts.productid', '=', 'products.id'):
- 执行一个INNER JOIN操作，将carts表与products表连接起来。
- 连接条件是carts.productid列与products.id列相等。
- 注意： 原生SQL中的WHERE carts.productid = products.id条件在Laravel Query Builder中通常通过join方法来处理，避免在where子句中重复写。
-
->select(DB::raw('SUM(carts.quantity) as couuntq'), 'carts.productid', ...):
- 这是实现聚合函数与多列选择的关键。
- DB::raw('SUM(carts.quantity) as couuntq'): DB::raw()方法允许您在查询中插入原始的SQL表达式。在这里，它将SUM(carts.quantity) as couuntq作为SELECT子句的一部分。as couuntq为聚合结果指定了一个别名，方便在结果集中访问。
- 紧随其后列出的 'carts.productid', 'products.name', 'products.price', 'products.gallary' 都是需要与聚合结果一同返回的常规列。
-
->where('carts.userid', $userid):
- 添加一个WHERE条件，过滤出特定用户（由$userid变量指定）的购物车记录。
-
->groupBy('carts.productid'):
- 使用GROUP BY子句对结果进行分组。当在SELECT子句中使用了聚合函数且同时选择了非聚合列时，所有非聚合列都必须出现在GROUP BY子句中（或者它们必须是函数式依赖于GROUP BY列）。这里我们按productid分组，以计算每个产品的总数量。
-
->orderBy('carts.productid', 'asc'):
- 对最终结果进行排序，这里是按照productid的升序排列。'asc'是默认值，可以省略。
-
->get():
- 执行查询并返回一个Collection对象，其中包含查询结果的StdClass对象列表。每个对象代表一行结果，可以通过属性访问列值（例如$item->couuntq）。
总结与最佳实践
- 聚合函数与多列选择： 当您需要在SELECT语句中同时使用聚合函数（如SUM, COUNT, AVG, MAX, MIN）并选择其他非聚合列时，务必使用DB::raw()来包装聚合函数表达式。
- GROUP BY的重要性： 如果SELECT子句中包含聚合函数和非聚合列，那么所有非聚合列都必须在GROUP BY子句中列出，以确保查询的逻辑正确性。
- JOIN与WHERE： 连接表的条件（例如carts.productid = products.id）应通过join()方法处理，而不是在where()方法中重复。where()方法主要用于筛选记录。
- 可读性与维护性： 尽管DB::raw()允许您插入原生SQL，但应尽可能地利用Query Builder的流畅API。只有当Query Builder无法直接表达复杂逻辑时，才使用DB::raw()。
- 安全性： 当使用DB::raw()插入用户输入时，务必进行适当的验证和清理，以防止SQL注入攻击。在本例中，SUM(carts.quantity)是硬编码的，所以没有直接的注入风险，但如果DB::raw()中包含变量，则需要格外小心。
通过遵循这些指南，您可以有效地将复杂的原生SQL查询转换为Laravel Query Builder的优雅语法，从而提高代码的可读性、可维护性，并充分利用Laravel框架的优势。
