Laravel Query Builder实现复杂联接、聚合查询及视图展示

本教程旨在指导用户如何使用Laravel Query Builder执行包含多表联接、复杂聚合函数及条件筛选的SQL查询，并正确地将结果展示在Blade视图中。文章将详细分析原始SQL与Query Builder的转换要点，纠正常见的语法错误，特别是`select`、`DB::raw()`和`havingRaw`的正确使用，并提供有效的调试策略，以解决如“Undefined variable”等问题。

1. 引言：Laravel Query Builder的强大与挑战

Laravel的查询构造器（Query Builder）提供了一种便捷、流式接口来构建和执行数据库查询。它抽象了底层数据库的差异，使开发者能够用PHP代码而非原生SQL来操作数据库。然而，当面对包含多表联接（JOIN）、复杂聚合函数（SUM, ROUND）、分组（GROUP BY）以及对聚合结果进行筛选（HAVING）的复杂查询时，将原生SQL语句准确无误地转换为Query Builder语法可能会遇到挑战。本教程将通过一个实际案例，深入探讨如何克服这些挑战。

2. 理解原始SQL查询

在开始使用Laravel Query Builder之前，我们首先需要彻底理解原始的SQL查询语句。这有助于我们明确查询的目标、涉及的表、联接条件、选择的列、聚合逻辑以及筛选规则。

以下是需要转换的原始SQL查询：

SELECT ru.external_ref_no AS SID, usd.user_name AS Username, rs.servicecode AS Package, rc.clientdesc as Entity, rc.clientip as NAS_IP,
ROUND((ROUND((SUM(usd.FREE_UPLOAD_OCTETS)/1048576)))/1024,2) AS Upload,
ROUND((ROUND((SUM(usd.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Download,
ROUND((ROUND((SUM(usd.FREE_UPLOAD_OCTETS)/1048576)))/1024,2) + ROUND((ROUND((SUM(usd.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Total_Usage
FROM user_session_detail usd, radservice rs, radclient rc, radgroup rg, raduser ru 
WHERE ru.username=usd.user_name AND rs.serviceid=usd.service_id AND rg.groupid=usd.group_id 
AND usd.client_id=rc.clientid AND usd.SESSION_START_TIME > '2021-09-30 00.00.01' AND usd.SESSION_START_TIME < '2021-09-30 23.59.59'
GROUP BY usd.user_name
HAVING (ROUND((SUM(usd.FREE_UPLOAD_OCTETS)/1048576)))/1024 + (ROUND((SUM(usd.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 > 15
AND (ROUND((SUM(usd.FREE_UPLOAD_OCTETS)/1048576)))/1024 + (ROUND((SUM(usd.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 < 20;

核心要素分析：

• 多表联接 (FROM / WHERE): 查询涉及 user_session_detail, radservice, radclient, radgroup, raduser 五个表，并通过 WHERE 子句中的等值条件进行隐式联接。
• 列选择与别名 (SELECT): 选择多个表的字段，并为聚合结果和部分字段设置了别名（如 SID, Username, Package, Upload, Download, Total_Usage）。
• 聚合函数与计算 (SUM, ROUND): 对 FREE_UPLOAD_OCTETS 和 FREE_DOWNLOAD_OCTETS 字段进行求和、字节单位转换（除以1048576转换为MB，再除以1024转换为GB）和四舍五入。
• 时间范围筛选 (WHERE): 根据 SESSION_START_TIME 字段筛选特定日期范围的数据。
• 分组 (GROUP BY): 根据 usd.user_name 进行分组，以便对每个用户的数据进行聚合。
• 聚合结果筛选 (HAVING): 对聚合后的 Total_Usage 进行二次筛选，要求其值在 15GB 到 20GB 之间。

3. Laravel Query Builder的常见陷阱与纠正

在将上述原生SQL转换为Laravel Query Builder时，可能会遇到以下常见问题：

3.1. select 方法的正确使用

Query Builder的 select 方法可以接受字符串、数组或 DB::raw() 表达式。当需要选择原始SQL表达式（如聚合函数）时，必须使用 DB::raw()。如果同时需要选择特定表的全部列和自定义表达式，应将它们合并到同一个 select 调用中，或者合理链式调用。

错误示例 (常见误区):

// 试图将所有列和DB::raw表达式分开，可能导致只选择最后一个select的内容
->select(array('user_session_detail.*')) 
->select(DB::raw('raduser.external_ref_no AS SID, ...')) 

// 或将DB::raw放在select数组中，但如果DB::raw内容过长，可读性差
->select(['user_session_detail.*', DB::raw('...')]) 

正确方式:

一帧秒创

基于秒创AIGC引擎的AI内容生成平台，图文转视频，无需剪辑，一键成片，零门槛创作视频。

下载

将所有需要选择的列和 DB::raw 表达式作为参数传递给单个 select 方法，或者使用 addSelect 方法追加选择。

->select('user_session_detail.*', DB::raw('raduser.external_ref_no AS SID, ...'))
// 或者
->select('user_session_detail.user_name', 'raduser.external_ref_no AS SID') // 选择特定列
->addSelect(DB::raw('ROUND((SUM(...))) AS Upload')) // 追加聚合列

3.2. having 与 havingRaw 的区分

• having() 方法用于对聚合结果进行简单的比较筛选，例如 ->having('total_users', '>', 100)。
• havingRaw() 方法则用于传入完整的原生SQL HAVING 子句表达式，这对于包含复杂计算或函数调用的筛选条件至关重要。

错误示例 (将复杂表达式直接传入 having 或 havingRaw 参数类型错误):

// 错误：having 方法不接受复杂的DB::raw表达式作为第一个参数
->having(DB::raw('(ROUND((SUM(...)))/1024) > 15'))

// 错误：havingRaw的参数必须是字符串，而不是PHP表达式
->havingRaw((ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024 + (ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 > 15) 

正确方式:

将完整的 HAVING 条件作为字符串传递给 havingRaw()。

->havingRaw('(ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024 + (ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 > 15 AND (ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 + (ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/10448576)))/1024 < 20')

3.3. join 方法中的表名拼写错误

这是最常见的错误之一，也是导致查询失败的隐蔽原因。例如，将 user_session_detail 拼写为 user_session_detai。这类错误会导致数据库无法找到指定的表或列，进而抛出SQL语法错误或查询结果为空，最终可能导致Blade视图中出现“Undefined variable”错误，因为查询未能成功执行并返回数据。

错误示例:

->join('radservice', 'user_session_detai.service_id', '=', 'radservice.serviceid') // 注意 'user_session_detai' 拼写错误

正确方式:

仔细核对所有表名和列名，确保与数据库中的定义完全一致。

->join('radservice', 'user_session_detail.service_id', '=', 'radservice.serviceid')

4. 完整的Laravel Query Builder实现

结合上述分析和纠正，以下是实现原始SQL查询的完整Laravel Query Builder代码：

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;

class ReportController extends Controller
{
    public function nsuresecret()
    {
        $user_session_detail = DB::table('user_session_detail')
            // 首先进行所有联接操作
            ->join('radservice', 'user_session_detail.service_id', '=', 'radservice.serviceid')
            ->join('radclient', 'user_session_detail.client_id', '=', 'radclient.clientid')
            ->join('radgroup', 'user_session_detail.group_id', '=', 'radgroup.groupid')
            ->join('raduser', 'user_session_detail.user_name', '=', 'raduser.username')
            // 筛选时间范围
            ->whereBetween('user_session_detail.SESSION_START_TIME', ['2021-09-30 00:00:01', '2021-09-30 23:59:59'])
            // 根据用户名分组
            ->groupBy('user_session_detail.user_name')
            // 选择需要的列，包括原始列和聚合计算列
            ->select(
                'user_session_detail.user_name', // 明确选择基础列，避免歧义
                DB::raw('
                    raduser.external_ref_no AS SID, 
                    user_session_detail.user_name AS Username, 
                    radservice.servicecode AS Package,
                    radclient.clientdesc as Entity, 
                    radclient.clientip as NAS_IP,
                    ROUND((ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024,2) AS Upload,
                    ROUND((ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Download,
                    ROUND((ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024,2) + ROUND((ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Total_Usage
                ')
            )
            // 对聚合结果进行筛选
            ->havingRaw('
                (ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024 + 
                (ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 > 15 
                AND 
                (ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024 + 
                (ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024 < 20
            ')
            ->get(); // 执行查询并获取结果集

        // 调试：在将数据传递给视图之前，检查数据是否正确
        // dd($user_session_detail);

        return view('reports.secretuserlist', compact('user_session_detail'));
    }
}

代码解释：

1. DB::table('user_session_detail'): 指定查询的主表。
2. ->join(...): 链式调用 join 方法来连接所有相关表。注意这里已经修正了 user_session_detail 的拼写错误。
3. ->whereBetween(...): 使用 whereBetween 方法方便地进行日期范围筛选。
4. ->groupBy('user_session_detail.user_name'): 指定分组依据的列。
5. ->select(...): 这是关键部分。
   • 为了清晰和避免潜在的列名冲突，我们明确选择了 user_session_detail.user_name。
   • 使用 DB::raw() 包含了所有复杂的聚合计算和别名，确保它们作为原始SQL表达式被执行。将长的 DB::raw 字符串格式化，提高可读性。
6. ->havingRaw(...): 将完整的 HAVING 条件作为字符串传递给 havingRaw 方法，用于筛选聚合后的结果。同样，为了可读性，对字符串进行了格式化。
7. ->get(): 执行查询并返回一个 Illuminate\Support\Collection 对象，其中包含查询结果。
8. return view('reports.secretuserlist', compact('user_session_detail')): 将查询结果 $user_session_detail 变量传递给名为 secretuserlist 的Blade视图。compact('user_session_detail') 等同于 ['user_session_detail' => $user_session_detail]。

5. 在Blade视图中展示数据

一旦控制器成功获取并传递了数据，Blade视图就可以轻松地迭代并展示这些数据。

<!-- resources/views/reports/secretuserlist.blade.php -->

<table class="table table table-bordered table-striped table-hover">
    <thead>
        <tr>
            <th>SID</th>
            <th>Username</th>
            <th>Package</th>
            <th>Entity</th>
            <th>NAS_IP</th>
            <th>Upload (GB)</th>
            <th>Download (GB)</th>
            <th>Total Usage (GB)</th>
        </tr>
    </thead>
    <tbody>
        @forelse($user_session_detail as $usd)
            <tr>
                <td>{{ $usd->SID }}</td>
                <td>{{ $usd->Username }}</td>
                <td>{{ $usd->Package }}</td>
                <td>{{ $usd->Entity }}</td>
                <td>{{ $usd->NAS_IP }}</td>
                <td>{{ $usd->Upload }}</td>
                <td>{{ $usd->Download }}</td>
                <td>{{ $usd->Total_Usage }}</td>
            </tr>
        @empty
            <tr>
                <td colspan="8">暂无数据</td>
            </tr>
        @endforelse
    </tbody>
</table>

视图代码解释：

• @forelse($user_session_detail as $usd): 这是一个Blade指令，用于迭代 $user_session_detail 集合。如果集合为空，则执行 @empty 块的内容。
• {{ $usd->SID }}: 通过对象属性访问方式 ($usd->列别名) 获取查询结果中的各个字段值。请注意，这里使用的别名（如 SID, Username 等）与 DB::raw() 中定义的别名保持一致。

6. 调试策略

当遇到“Undefined variable”或其他查询相关问题时，有效的调试至关重要。

• 使用 dd() (Dump and Die): 在控制器中，将 dd($user_session_detail); 放置在 return view(...) 语句之前。这会停止脚本执行并打印 $user_session_detail 变量的内容。如果查询失败，dd() 可能会显示错误信息，或者如果查询返回空集合，则会显示空集合。
• 查看生成的SQL: Query Builder提供了 toSql() 方法来查看它将要执行的SQL语句。

  $query = DB::table('user_session_detail')
      // ... (your query chain)
      ->toSql();
  dd($query);

  这可以帮助你检查生成的SQL是否与你期望的原始SQL一致。

• 查看绑定参数: 结合 toSql()，getBindings() 方法可以显示SQL语句中绑定的参数。

  $bindings = DB::table('user_session_detail')
      // ... (your query chain)
      ->getBindings();
  dd($bindings);

  这对于调试 where 或 having 子句中的参数问题非常有用。

7. 总结与最佳实践

• 理解原生SQL: 在将复杂SQL转换为Query Builder之前，务必彻底理解其逻辑。
• DB::raw() 的应用: 对于聚合函数、复杂表达式或数据库特定函数，始终使用 DB::raw()。