本教程旨在指导用户如何使用Laravel Query Builder执行包含多表联接、复杂%ignore_a_1%及条件筛选的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_UsageFROM 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 15AND (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('...')])
正确方式:
将所有需要选择的列和 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代码：
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 get(); // 执行查询并获取结果集 // 调试：在将数据传递给视图之前，检查数据是否正确 // dd($user_session_detail); return view('reports.secretuserlist', compact('user_session_detail')); }}
代码解释：
DB::table(‘user_session_detail’): 指定查询的主表。->join(…): 链式调用 join 方法来连接所有相关表。注意这里已经修正了 user_session_detail 的拼写错误。->whereBetween(…): 使用 whereBetween 方法方便地进行日期范围筛选。->groupBy(‘user_session_detail.user_name’): 指定分组依据的列。->select(…): 这是关键部分。为了清晰和避免潜在的列名冲突，我们明确选择了 user_session_detail.user_name。使用 DB::raw() 包含了所有复杂的聚合计算和别名，确保它们作为原始SQL表达式被执行。将长的 DB::raw 字符串格式化，提高可读性。->havingRaw(…): 将完整的 HAVING 条件作为字符串传递给 havingRaw 方法，用于筛选聚合后的结果。同样，为了可读性，对字符串进行了格式化。->get(): 执行查询并返回一个 IlluminateSupportCollection 对象，其中包含查询结果。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视图就可以轻松地迭代并展示这些数据。
| SID | Username | Package | Entity | NAS_IP | Upload (GB) | Download (GB) | Total Usage (GB) |
|---|---|---|---|---|---|---|---|
| {{ $usd->SID }} | {{ $usd->Username }} | {{ $usd->Package }} | {{ $usd->Entity }} | {{ $usd->NAS_IP }} | {{ $usd->Upload }} | {{ $usd->Download }} | {{ $usd->Total_Usage }} |
| 暂无数据 | |||||||
视图代码解释：
@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()。
以上就是Laravel Query Builder实现复杂联接、聚合查询及视图展示的详细内容，更多请关注php中文网其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1334600.html
微信扫一扫 
支付宝扫一扫 
