利用Laravel Query Builder实现复杂多表联接与数据聚合-php教程-PHP中文网

利用laravel query builder实现复杂多表联接与数据聚合

本教程详细阐述了如何使用Laravel Query Builder将复杂的原始SQL查询（涉及多表联接、聚合函数、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_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)：选择了来自不同表的多个列，并为聚合计算结果定义了别名。
聚合函数 (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 Illuminate\Support\Facades\DB;

// ... 在控制器方法中 ...
$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表达式。

飞书多维表格

表格形态的AI工作流搭建工具，支持批量化的AI创作与分析任务，接入DeepSeek R1满血版

查看详情

$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代码示例

将上述所有部分整合到控制器方法中，如下所示：

<?php

namespace App\Http\Controllers;

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

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

        // 将数据传递给视图
        return view('reports.secretuserlist', compact('user_session_detail'));
    }
}

登录后复制

4. 在Blade视图中显示数据

控制器将$user_session_detail变量通过compact()函数传递给视图。在Blade模板中，可以通过循环遍历这个集合来显示数据。

<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</th>
            <th>Download</th>
            <th>Total_Usage</th>
        </tr>
    </thead>
    <tbody>
        @foreach($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>
        @endforeach
    </tbody>
</table>

登录后复制

常见错误：“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-youjiankuohaophpcngetBindings());。
检查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可能更为直接和高效。