Laravel Query Builder 多表联接、聚合与复杂条件查询指南

DDD

发布时间：2025-11-14 10:55:01

213人浏览过

来源于php中文网

原创

Laravel Query Builder 多表联接、聚合与复杂条件查询指南

本教程详细介绍了如何使用 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_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;

该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 < 20)
            ->get();

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

2.2 优化后的Query Builder代码

为了正确实现原始SQL的功能并避免潜在错误，我们可以按照以下方式优化Query Builder代码。主要修正包括：

FlowIn

AI 时代下的内容共创平台，借助 FlowIn AI 撰写与改写内容十分便利。

下载

将 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 < 20')
            // 执行查询并获取结果集
            ->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 集合，并访问每个对象的属性来展示数据。


        @foreach($user_session_detail as $usd)
            
        @endforeach
    
    
        
            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 }}
            


@if($user_session_detail->isEmpty())
    没有找到符合条件的用户会话详情。
@endif

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 }}