Laravel查询构造器多表联查出现Undefined variable $user_session_detail错误如何解决

• 错误1：关联查询时表名拼写错误
  你写的->join('radservice', 'user_session_detai.service_id', '=', 'radservice.serviceid')里user_session_detai少了末尾的l，正确应为user_session_detail
• 错误2：havingRaw方法参数未用引号包裹
  havingRaw接收SQL字符串作为参数，你直接将表达式写入会被PHP解析为代码执行，导致语法错误，变量$user_session_detail未成功赋值，模板就会提示变量未定义
• 错误3：Blade模板字段名大小写不匹配
  你在查询中给用户名字段设置的别名是Username，但模板中写的是$usd->UserName，名称不一致会导致后续页面输出报错
• 可选优化：select语句中多余的user_session_detail.*可以删除，MySQL开启only_full_group_by模式时，查询非分组非聚合字段会抛出SQL错误，同时原SQL的时间分隔符应为冒号而非点，否则会导致查询无结果

修正后的代码 #

1. 控制器查询构造器代码 #

public function nsuresecret(){
    $user_session_detail = DB::table('user_session_detail')
        ->select(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_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')
        ->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. 修正后的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>

内容的提问来源于stack exchange，提问作者Abdullah AL samir