-2

Could anyone please help me to build the following query into the Laravel Query Builder. I only know how to query into the database using 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;
RaceTech
  • 71
  • 6

1 Answers1

1

Your SQL query is very customized and can not be written by eloquent and query builder so you can run it as a raw SQL like this:

$results = DB::select("... your customized SQL query ...");

then you can pass the variable $results to your blade:

return view('reports.secretuserlist',compact('results'));

in the blade, you can access the data via the $results variable

Mehrdad Gharibdoost
  • 196
  • 1
  • 6
  • Is the following line okay to pass the value to blade? Because i'm getting an error ""UserSessionDetail" Undefined Variable return view('reports.secretuserlist',compact('$userSessionDetail')); – RaceTech Dec 07 '21 at 10:03
  • @RaceTech your result will be an array and its ok to pass the value to balade like this: view('reports.secretuserlist',compact('results')). note that the argument in compact is not a variable, it is string (without $) – Mehrdad Gharibdoost Dec 07 '21 at 10:59
  • Without return view, i also tried to get the output using "dd" but still not working. Could you please advice on this and write the output code for me. It would be a great help. – RaceTech Dec 07 '21 at 12:36