本教程详细介绍了如何使用 laravel query builder 实现复杂的数据库查询，包括多表联接、数据聚合（sum、round）、分组（group by）以及基于聚合结果的条件筛选（having）。文章将通过一个具体的案例，指导开发者如何将原始sql语句转换为query builder代码，并解决在数据传递至视图时可能出现的“undefined variable”错误，确保数据能够正确获取并在blade模板中展示。
1. 原始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 ruWHERE ru.username=usd.user_name AND rs.serviceid=usd.service_id AND rg.groupid=usd.group_idAND 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;
该SQL查询的核心功能包括：
多表联接: user_session_detail (别名 usd) 与 radservice (rs), radclient (rc), radgroup (rg), raduser (ru) 进行联接。选择与别名: 选择特定字段并赋予别名，例如 ru.external_ref_no AS SID。数据聚合: 使用 SUM() 和 ROUND() 函数计算用户的上传、下载及总使用量，并转换为GB单位，保留两位小数。时间范围筛选: WHERE 子句根据 SESSION_START_TIME 筛选特定日期的数据。分组: GROUP BY usd.user_name 按用户名对结果进行分组。聚合后筛选: HAVING 子句对聚合后的总使用量进行进一步筛选，只返回总使用量在15GB到20GB之间的用户数据。
2. Laravel Query Builder 实现
将上述复杂的SQL语句转换为Laravel Query Builder代码时，需要注意联接顺序、select 语句的构造以及 having 子句的使用。
2.1 初始尝试与常见问题
在将原始SQL转换为Query Builder时，开发者可能会遇到一些问题，例如：
select 语句的放置和结构: 当同时需要选择普通列和使用 DB::raw 进行复杂计算的列时，其组合方式需要特别注意。联接条件中的拼写错误: join 方法中的表名或字段名拼写错误，例如 user_session_detai 而非 user_session_detail。havingRaw 的使用: 复杂的聚合条件需要使用 havingRaw 来直接写入SQL片段。
以下是原始问题中提供的初始Laravel Query Builder代码，其中包含了一些需要修正的地方：
public function nsuresecret(){ $user_session_detail = DB::table('user_session_detail') ->select(array('user_session_detail.*'), 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')) ->join('radservice', 'user_session_detai.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') ->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')); }
2.2 优化后的Query Builder代码
为了正确实现原始SQL的功能并避免潜在错误，我们可以按照以下方式优化Query Builder代码。主要修正包括：
将 select 语句放在所有 join 和 where 条件之后，这有助于确保在选择列时所有联接关系都已建立。修正 join 条件中的拼写错误：user_session_detai 应为 user_session_detail。havingRaw 中的条件应作为一个完整的字符串传入。
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') // 选择需要的列，包括通过DB::raw实现的复杂聚合计算 ->select( 'user_session_detail.*', // 如果需要user_session_detail表的所有原始列，可以包含此行 // 注意：原始SQL只选择了特定别名列，如果不需要所有原始列，可以移除此行 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')); }}
注意事项：
DB::raw() 用于插入原生的SQL表达式，这在执行复杂计算或使用数据库特定函数时非常有用。whereBetween() 方法用于筛选一个范围内的值，非常适合日期或数字范围查询。havingRaw() 方法用于对 GROUP BY 后的聚合结果进行筛选，其参数必须是完整的SQL条件字符串。select(‘user_session_detail.*’, …) 会选择 user_session_detail 表的所有列以及 DB::raw 定义的聚合列。如果原始SQL只希望返回聚合列，则可以省略 ‘user_session_detail.*’。
3. 视图层数据展示
在控制器中获取到数据后，需要将其传递给Blade视图进行展示。compact(‘user_session_detail’) 方法会将 $user_session_detail 变量以同名键值对的形式传递给视图。
3.1 Blade模板代码
在视图文件 resources/views/reports/secretuserlist.blade.php 中，可以使用 @foreach 循环遍历 $user_session_detail 集合，并访问每个对象的属性来展示数据。
| 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 }} |
没有找到符合条件的用户会话详情。
@endif注意事项：
Blade模板中的变量名必须与控制器中通过 compact() 或 with() 方法传递的变量名保持一致。例如，控制器中传递的是 $user_session_detail
以上就是Laravel Query Builder 多表联接、聚合与复杂条件查询指南的详细内容，更多请关注php中文网其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1334193.html
微信扫一扫 
支付宝扫一扫 
