在 Laravel 中实现 LEFT JOIN 和 SUM 聚合查询

场景概述：计算购物车总价

在 web 应用开发中，尤其是在电商领域，计算购物车中商品的总价是一个非常常见的需求。这个计算通常涉及多个数据表：一个表存储购物车条目（例如 carts），另一个表存储商品信息（例如 food），其中包含商品的价格。我们需要将这两个表关联起来，然后根据每个商品的数量和价格计算总和。

假设我们有以下两个表结构：

• carts 表: 存储用户的购物车条目。
  • id: 购物车条目ID
  • user_id: 用户ID
  • food_id: 商品ID
  • quantity: 商品数量
  • status: 购物车条目状态（例如 '0' 表示未下单）
• food 表: 存储商品信息。
  • id: 商品ID
  • name: 商品名称
  • price: 商品单价

目标是计算特定用户购物车中所有处于未下单状态的商品总价。其对应的原生 SQL 查询如下：

SELECT sum(food.price * carts.quantity) as total 
FROM carts  
LEFT JOIN food ON carts.food_id = food.id 
WHERE user_id = $user_id AND status = '0';

接下来，我们将展示如何在 Laravel 中使用 Query Builder 实现这一查询。

使用 Laravel Query Builder 实现 LEFT JOIN 和 SUM 聚合

Laravel 的 Query Builder 提供了一种流畅、面向对象的方式来构建和执行数据库查询，避免了编写冗长的原生 SQL 语句，同时提供了防止 SQL 注入的保护。

要实现上述查询，我们可以按照以下步骤构建 Laravel Query Builder 语句：

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情

1. 选择主表: 使用 DB::table() 方法指定查询的主表，这里是 carts 表。
2. 执行 LEFT JOIN: 使用 leftJoin() 方法连接 food 表。第一个参数是连接的表名，随后的两个参数定义了连接条件，即 carts.food_id 等于 food.id。
3. 聚合计算: 使用 select() 方法来指定需要返回的列。由于我们需要执行 SUM(food.price * carts.quantity) 这样的聚合计算，并且要给结果一个别名 total，我们必须使用 DB::raw() 方法来插入原生 SQL 表达式。DB::raw() 允许你在 Query Builder 中使用任意的原生 SQL 片段。
4. 添加 WHERE 条件: 使用 where() 方法添加筛选条件，包括 user_id 和 status。为了明确，建议使用表名作为前缀，例如 carts.user_id。
5. 获取结果: 使用 first() 方法获取查询结果的第一条记录。由于我们执行的是聚合查询，通常只会返回一条记录，其中包含计算出的总和。

以下是完整的 Laravel Query Builder 代码示例：

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth; // 引入 Auth Facade

class CartController extends Controller
{
    public function calculateCartTotal()
    {
        // 获取当前认证用户的ID
        $user_id = Auth::id();

        // 使用 Laravel Query Builder 执行查询
        $result = DB::table('carts')
                    ->select(DB::raw('SUM(food.price * carts.quantity) AS total'))
                    ->leftJoin('food', 'carts.food_id', '=', 'food.id') // 注意这里是 'food.id' 而不是 'foods.id'
                    ->where('carts.user_id', $user_id)
                    ->where('carts.status', 0) // 假设 '0' 代表未下单状态
                    ->first();

        // 检查结果并获取总价
        $totalPrice = $result ? $result->total : 0;

        return "用户ID {$user_id} 的购物车总价为: {$totalPrice}";
    }
}

在上述代码中，DB::raw('SUM(food.price * carts.quantity) AS total') 是实现聚合计算的关键。它告诉 Laravel Query Builder 直接将括号内的字符串作为 SQL 表达式插入到 SELECT 子句中。

注意事项与最佳实践

• DB::raw() 的使用: DB::raw() 是处理复杂 SQL 表达式（如聚合函数、子查询、CASE 语句等）的强大工具。但请注意，DB::raw() 中的内容不会被 Laravel 自动转义，因此在使用用户输入时，务必确保对其进行适当的清理，以防止 SQL 注入。在本例中，food.price * carts.quantity 是固定的列名和操作，因此是安全的。
• 表名和列名: 在 leftJoin() 和 where() 条件中，明确指定表名作为列名的前缀（例如 carts.user_id）是一个好习惯，可以提高查询的可读性，并避免在多表查询中出现列名冲突。
• 结果处理: first() 方法返回一个 StdClass 对象，你可以通过属性访问结果（例如 $result-youjiankuohaophpcntotal）。如果查询没有结果（例如购物车为空），first() 将返回 null。因此，在访问结果之前进行非空检查是一个良好的编程习惯。
• Eloquent ORM: 虽然本例使用了 Query Builder，但在许多情况下，如果你的模型之间定义了 Eloquent 关系（例如 Cart 模型属于 User，并且 Cart 属于 Food），你也可以考虑使用 Eloquent 的关系方法来简化查询。然而，对于这种跨多个关联表的复杂聚合，Query Builder 往往更直接和高效。
• 性能考量: 对于大型数据集，确保 food_id 和 user_id 列上存在索引，这将显著提高 JOIN 和 WHERE 子句的查询性能。

总结

Laravel 的 Query Builder 提供了一种灵活且强大的方式来构建复杂的数据库查询。通过熟练运用 DB::table()、leftJoin()、where() 和 DB::raw() 等方法，开发者可以高效地处理多表关联和聚合计算的需求。理解并正确应用这些工具，将有助于你编写出更健壮、可维护的 Laravel 应用。

以上就是在 Laravel 中实现 LEFT JOIN 和 SUM 聚合查询的详细内容，更多请关注php中文网其它相关文章！