Laravel Query Builder多表联查与聚合数据处理教程-php教程-PHP中文网

laravel query builder多表联查与聚合数据处理教程

本教程详细阐述了如何在Laravel框架中使用Query Builder进行复杂的数据库操作，包括多表联查、聚合函数应用、条件筛选以及数据分组。通过优化查询结构和调试方法，解决在视图中数据展示时可能遇到的“未定义变量”等常见问题，确保数据准确高效地从数据库提取并渲染到前端页面。

1. 概述与需求分析

在Web应用开发中，从多个相关联的数据库表中提取并聚合数据是常见的需求。Laravel的Query Builder提供了一种流畅且强大的方式来构建SQL查询，无需编写原始SQL语句即可实现复杂的数据操作。本教程将通过一个具体示例，演示如何利用Query Builder执行多表联查、应用聚合函数（如SUM、ROUND）、设置分组（GROUP BY）和分组条件（HAVING），最终将处理后的数据展示在Blade视图中。

我们将分析一个原始SQL查询，并将其逐步转换为Laravel Query Builder的实现，同时解决开发过程中可能出现的“未定义变量”等问题。

2. 原始SQL查询解析

首先，我们来看一个需要通过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;

登录后复制

该SQL查询的核心要素包括：

多表联查 (FROM/WHERE): user_session_detail, radservice, radclient, radgroup, raduser 五个表通过各自的主外键进行关联。
选择列与别名 (SELECT AS): 选择了来自不同表的多个字段，并为聚合计算结果赋予别名（如SID, Username, Upload, Download, Total_Usage）。
聚合函数与计算 (SUM, ROUND): 对上传和下载字节数进行求和，并转换为GB单位，保留两位小数。
时间范围筛选 (WHERE): 筛选特定日期范围内的会话数据。
分组 (GROUP BY): 按照user_name进行分组，以便对每个用户的流量进行聚合。
分组条件筛选 (HAVING): 在分组聚合之后，对总使用量在15GB到20GB之间的用户进行二次筛选。

3. 使用Laravel Query Builder实现

将上述复杂的原始SQL查询转换为Laravel Query Builder需要遵循一定的结构和方法。

3.1 控制器中的查询构建

在Laravel控制器中，我们可以使用DB facade来构建查询。关键步骤包括：

指定主表: 使用 DB::table() 指定查询的起始表。
多表联接 (JOIN): 使用 join() 方法连接其他相关表，并指定联接条件。
选择列与原始表达式 (SELECT, DB::raw()): 定义需要查询的列，对于复杂的聚合函数和计算，需要使用 DB::raw() 来直接插入原始SQL表达式。
条件筛选 (WHERE): 使用 whereBetween() 等方法添加查询条件。
数据分组 (GROUP BY): 使用 groupBy() 方法对结果进行分组。
分组后条件筛选 (HAVING RAW): 对于聚合后的条件筛选，使用 havingRaw() 方法。
执行查询: 最后使用 get() 方法执行查询并获取结果集。

以下是优化后的控制器方法示例：

<?php

namespace App\Http\Controllers;

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

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')
            // 选择列，包括原始SQL表达式进行聚合计算和别名
            ->select(
                'user_session_detail.*', // 如果需要 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')
            )
            // 分组后条件筛选
            ->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'));
    }
}

登录后复制

关键优化点与注意事项：

百度文心一格

百度推出的AI绘画作图工具

112

查看详情

join() 顺序: 联接操作应在 select() 之前定义，以确保在选择列时可以正确引用所有联接表中的字段。
select() 与 DB::raw(): 当需要复杂的SQL表达式（如聚合函数、数学计算、多个列的组合）时，DB::raw() 是必不可少的。它允许你直接插入原始SQL片段。
havingRaw(): 对于 HAVING 子句，由于它通常包含聚合函数，因此需要使用 havingRaw() 来插入原始SQL表达式。
日期格式: whereBetween 的日期字符串应与数据库的日期时间格式匹配，或者使用Carbon实例以获得更好的兼容性。
变量名与视图传递: 确保控制器中定义的变量名（例如 $user_session_detail）与 compact() 函数中使用的名称以及Blade视图中访问的名称完全一致。

3.2 视图层数据展示 (Blade)

在Blade视图中，你可以像处理任何集合一样迭代查询结果，并通过对象属性访问每个字段（包括通过 AS 定义的别名）。

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

登录后复制

4. 常见问题与调试

在构建复杂查询时，可能会遇到各种问题。

4.1 “Undefined variable” 错误

问题现象: 在Blade视图中出现 Undefined variable $user_session_detail 错误。 原因分析:

控制器中变量未定义或赋值失败: 最常见的原因是控制器中的 $user_session_detail 变量在 return view(...) 之前未能成功赋值。这可能是因为Query Builder本身在执行时抛出了异常（例如SQL语法错误、表或列名错误），导致代码提前终止或变量未被赋值。
compact() 参数错误: compact('user_session_detail') 中的字符串与实际变量名不匹配。
视图中变量名拼写错误: 在Blade模板中访问变量时，拼写错误。

调试方法: 在控制器中 return view(...) 语句之前，使用 dd($user_session_detail); 或 dump($user_session_detail); 来检查变量是否被正确赋值以及其内容。如果 dd() 导致页面空白或错误，则说明问题出在查询构建阶段。

// ... (Query Builder 代码) ...
$user_session_detail = DB::table('user_session_detail')
    // ...
    ->get();

dd($user_session_detail); // 检查查询结果
return view('reports.secretuserlist', compact('user_session_detail'));

登录后复制

4.2 SQL语法或逻辑错误

问题现象: 查询结果不正确，或者数据库抛出SQL错误。 调试方法:

检查生成的SQL: 使用 toSql() 方法可以获取Query Builder生成的原始SQL语句，然后可以在数据库客户端中直接运行此SQL进行测试。

$query = DB::table('user_session_detail')
    // ... (省略部分查询链) ...
    ->toSql(); // 注意：toSql() 不会执行查询，也不会返回绑定参数
dd($query);

登录后复制

获取绑定参数: Query Builder会安全地绑定参数。要获取完整的SQL语句（包括绑定参数），可以结合 getBindings() 方法。

$builder = DB::table('user_session_detail')
    // ... (查询链) ...
    ->whereBetween('user_session_detail.SESSION_START_TIME', ['2021-09-30 00:00:01', '2021-09-30 23:59:59']);

$sql = $builder->toSql();
$bindings = $builder->getBindings();

// 手动替换绑定参数（仅用于调试显示，不推荐在生产环境直接拼接）
foreach ($bindings as $binding) {
    $sql = preg_replace('/\?/', "'" . $binding . "'", $sql, 1);
}
dd($sql); // 打印带有实际参数的SQL

登录后复制

或者更简单地，直接使用 DB::enableQueryLog() 和 DB::getQueryLog() 来查看最近执行的查询。

DB::enableQueryLog();
$user_session_detail = DB::table('user_session_detail')
    // ...
    ->get();
dd(DB::getQueryLog()); // 查看所有执行的查询及参数

登录后复制

检查表名和列名: 仔细核对代码中使用的表名和列名是否与数据库中的实际名称一致，特别是别名。

5. 总结

通过本教程，我们学习了如何利用Laravel Query Builder构建复杂的数据库查询，包括多表联查、聚合计算、条件筛选和分组。掌握 DB::table(), join(), select(), DB::raw(), whereBetween(), groupBy(), havingRaw() 和 get() 等方法是高效使用Query Builder的关键。同时，了解并运用 dd()、toSql() 和 getQueryLog() 等调试技巧，能有效帮助我们定位和解决开发过程中遇到的问题，确保数据查询的准确性和程序的稳定性。在实际开发中，应始终优先考虑使用Query Builder而非原始SQL，以提高代码的可维护性和安全性。

以上就是Laravel Query Builder多表联查与聚合数据处理教程的详细内容，更多请关注php中文网其它相关文章！

大家都在看：

PHP中动态嵌套数组特定元素的修改教程 PHP数组重构技巧：聚合数据库查询结果中的重复项如何通过PHP调用SOAP服务_PHP SOAP服务调用与WS-Security实现教程 PHP 通过 FTP 下载文件教程 PHP API开发中JSON响应前的HTML输出问题解析与解决方案