如何将指定原生SQL查询转换为Laravel Query Builder实现

<?php

$usageData = \DB::table('user_session_detail as usd')
    ->selectRaw('
        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
    ')
    ->join('radservice as rs', 'rs.serviceid', '=', 'usd.service_id')
    ->join('radclient as rc', 'rc.clientid', '=', 'usd.client_id')
    ->join('radgroup as rg', 'rg.groupid', '=', 'usd.group_id')
    ->join('raduser as ru', 'ru.username', '=', 'usd.user_name')
    ->where('usd.SESSION_START_TIME', '>', '2021-09-30 00:00:01')
    ->where('usd.SESSION_START_TIME', '<', '2021-09-30 23:59:59')
    ->groupBy('usd.user_name')
    ->havingRaw('
        (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
    ')
    ->get();

注意事项 #

• 原SQL中的隐式多表关联统一转换为显式join写法，逻辑和原SQL完全一致，可读性更高
• 所有涉及聚合计算、数据库原生函数的部分都使用selectRaw和havingRaw处理，避免Laravel查询构造器自动解析语法出错
• 原SQL中的时间分隔符从.修正为标准的:，符合MySQL时间格式规范，如果你的数据库实际存储的时间格式用.作为分隔符，可以改回原来的写法
• 如果你的MySQL开启了ONLY_FULL_GROUP_BY模式，需要把selectRaw中除了聚合函数之外的所有字段都加到groupBy的参数里，否则会报分组错误

内容的提问来源于stack exchange，提问作者RaceTech