本教程详细阐述了如何使用Laravel Query Builder将复杂的原始SQL查询（涉及多表联接、%ignore_a_1%、GROUP BY和HAVING子句）转换为可维护的PHP代码。文章通过具体示例，指导读者构建高效的数据库查询，并解决了在视图中传递和显示数据时常见的“未定义变量”错误，同时提供了调试技巧和最佳实践。
1. 理解复杂SQL查询的结构
在将复杂的原始SQL查询转换为Laravel Query Builder代码之前，首先需要深入理解其各个组成部分。以下是原始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)：选择了来自不同表的多个列，并为聚合计算结果定义了别名。聚合函数 (SUM, ROUND)：计算上传、下载和总使用量的总和，并进行单位转换和四舍五入。条件过滤 (WHERE)：基于SESSION_START_TIME进行时间范围过滤。分组 (GROUP BY)：根据user_name对结果进行分组。聚合后过滤 (HAVING)：在分组和聚合之后，根据总使用量再次进行过滤。
2. 使用Laravel Query Builder构建查询
Laravel Query Builder提供了一种流畅且面向对象的方式来构建和执行数据库查询。以下是将上述复杂SQL转换为Query Builder的步骤和示例代码。
2.1 初始化查询与表联接
首先，通过DB::table()指定主表，然后使用join()方法进行多表联接。join()方法接受三个参数：要联接的表名、第一个联接列和第二个联接列。
use IlluminateSupportFacadesDB;// ... 在控制器方法中 ...$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');
注意事项： 在原始问题中，user_session_detai.service_id存在拼写错误，已更正为user_session_detail.service_id。仔细检查联接条件中的表名和列名是避免错误的关键。
2.2 添加条件过滤 (WHERE)
对于时间范围过滤，Query Builder提供了whereBetween()方法，它接受一个列名和包含开始/结束值的数组。
$user_session_detail = DB::table('user_session_detail') // ... joins ... ->whereBetween('user_session_detail.SESSION_START_TIME', ['2021-09-30 00.00.01', '2021-09-30 23.59.59']);
2.3 定义分组 (GROUP BY)
使用groupBy()方法指定用于分组的列。
$user_session_detail = DB::table('user_session_detail') // ... joins and where ... ->groupBy('user_session_detail.user_name');
2.4 选择列与聚合函数 (SELECT)
对于复杂的聚合函数和原始SQL表达式，应使用DB::raw()方法。select()方法可以接受多个参数，包括普通列名和DB::raw()表达式。通常建议将select语句放在join和where之后，groupBy之前或之后都可以，但放在groupBy之后可以确保在聚合发生后选择正确的列。
$user_session_detail = DB::table('user_session_detail') // ... joins, where, groupBy ... ->select( 'user_session_detail.*', // 如果需要 user_session_detail 表的所有列 DB::raw('raduser.external_ref_no AS SID'), DB::raw('user_session_detail.user_name AS Username'), DB::raw('radservice.servicecode AS Package'), DB::raw('radclient.clientdesc as Entity'), DB::raw('radclient.clientip as NAS_IP'), DB::raw('ROUND((ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024,2) AS Upload'), DB::raw('ROUND((ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Download'), DB::raw('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') );
提示： 当使用DB::raw()时，确保将完整的SQL表达式作为字符串传递。
2.5 聚合后过滤 (HAVING)
HAVING子句用于在GROUP BY操作之后过滤分组结果。由于HAVING条件通常涉及聚合函数，因此应使用havingRaw()方法，它允许您直接编写原始SQL的HAVING表达式。
$user_session_detail = DB::table('user_session_detail') // ... joins, where, groupBy, select ... ->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');
注意事项： havingRaw()方法期望一个字符串作为参数，该字符串包含完整的SQL HAVING条件。
2.6 执行查询并获取结果
最后，调用get()方法来执行查询并获取一个Collection对象，其中包含查询结果。
$results = $user_session_detail->get();
3. 完整的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_session_detail 表的所有列 DB::raw('raduser.external_ref_no AS SID'), DB::raw('user_session_detail.user_name AS Username'), DB::raw('radservice.servicecode AS Package'), DB::raw('radclient.clientdesc as Entity'), DB::raw('radclient.clientip as NAS_IP'), DB::raw('ROUND((ROUND((SUM(user_session_detail.FREE_UPLOAD_OCTETS)/1048576)))/1024,2) AS Upload'), DB::raw('ROUND((ROUND((SUM(user_session_detail.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Download'), DB::raw('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(); // 将数据传递给视图 return view('reports.secretuserlist', compact('user_session_detail')); }}
4. 在Blade视图中显示数据
控制器将$user_session_detail变量通过compact()函数传递给视图。在Blade模板中，可以通过循环遍历这个集合来显示数据。
| SID | Username | Package | Entity | NAS_IP | Upload | Download | Total_Usage |
|---|---|---|---|---|---|---|---|
| {{ $usd->SID }} | {{ $usd->Username }} | {{ $usd->Package }} | {{ $usd->Entity }} | {{ $usd->NAS_IP }} | {{ $usd->Upload }} | {{ $usd->Download }} | {{ $usd->Total_Usage }} |
常见错误：“Undefined variable $user_session_detail”这个错误通常发生在以下情况：
变量名不匹配： 控制器中compact()函数传递的变量名与Blade视图中使用的变量名不一致。请确保两者完全相同。查询失败或返回空： 如果数据库查询本身失败或没有返回任何结果，$user_session_detail可能为null或空集合，但在Blade中尝试访问其属性时可能会导致此错误。控制器方法未被访问： 确保路由正确配置，并且控制器方法被正确调用。
5. 调试技巧
当遇到“Undefined variable”或其他查询问题时，以下调试技巧非常有用：
使用 dd() 或 dump()： 在将数据传递给视图之前，使用dd($user_session_detail);（dd代表”dump and die”）或dump($user_session_detail);来检查查询结果。这会停止脚本执行并显示变量的详细内容，帮助您确认数据是否按预期返回。
// ... 在控制器方法中，在 return view() 之前dd($user_session_detail); return view('reports.secretuserlist', compact('user_session_detail'));
查看原始SQL： Query Builder允许您查看生成的SQL语句，这对于调试复杂查询非常有帮助。
$user_session_detail = DB::table('user_session_detail') // ... all query builder methods ... ->toSql(); // 返回生成的SQL字符串，而不是执行查询dd($user_session_detail);
如果您需要查看绑定参数，可以使用dd($user_session_detail->getBindings());。
检查Laravel日志： Laravel会将错误信息记录到storage/logs目录下的日志文件中。检查这些日志可以发现潜在的数据库连接问题或SQL语法错误。
6. 总结与最佳实践
明确联接条件： 始终明确指定join方法的联接条件，避免隐式联接。利用 DB::raw()： 对于复杂的SQL函数、聚合表达式或子查询，DB::raw()是不可或缺的工具。havingRaw() vs having()： 当HAVING子句包含聚合函数时，使用havingRaw()。如果HAVING条件仅涉及已选择的列（非聚合结果），则可以使用having()。调试是关键： 掌握dd()、dump()和toSql()等调试工具，能大大提高解决问题的效率。代码可读性： 保持Query Builder链式调用的清晰和一致性，有助于代码的维护和理解。考虑Eloquent ORM： 对于更复杂的数据库交互和模型关系，Eloquent ORM通常是更优雅的选择。然而，对于特定且复杂的聚合查询，Query Builder可能更为直接和高效。
通过遵循这些指南，您可以有效地在Laravel应用程序中构建和管理复杂的数据库查询，确保数据的准确性并提高开发效率。
以上就是利用Laravel Query Builder实现复杂多表联接与数据聚合的详细内容，更多请关注php中文网其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1333385.html
微信扫一扫 
支付宝扫一扫 
