Join in laravel query builder and show output-CodePudding

I try to get data from database by using laravel query builder and show the data. But getting below error "Undefined variable $user_session_detail". I give all the code below that i prepare.

//My raw query

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;

//Laravel query builder query

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'));
    }

//Blade code

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

CodePudding user response：

Change your $users variable to $user_session_detail

CodePudding user response：

You should check if compact('user_session_detail') has key user_session_detail