[lavarel]执行原生sql,与框架内置方法查询

$where = TranslatorOnline::queryFormat($request);
$rows = TranslatorOnline::searchList($where,'a.*,b.iol_code,b.nickname,b.true_name,b.mother_tongue,b.first_language,(sum(duration_time)/(1000*60)) as total_time,DATE_FORMAT(a.create_time,\'%Y-%m-%d\') as _my_date',$offset,$limit);

//注意封装的select方法与get方法在传值上的差异，select(string),get(array),一个是字符串，一个是数组

上代码
//查询列表
public static function searchList($where,$fields = ['*'],$offset,$pageSize){
　　/**注释掉的部分是执行原生sql的方式**/
    //$where_str = [];
    if(!empty($where)){
        /*foreach($where as $key => $val){
            if(in_array($val['0'],array('true_name'))){
                $where_str[] = 'b.'.$val['0']."  ".$val['1']."  '".$val['2']."'";
            } else {
                $where_str[] = 'a.'.$val['0']."  ".$val['1']."  '".$val['2']."'";
            }
        }*/
        foreach($where as $key => $val){
            if(in_array($val['0'],array('true_name'))){
                $where[$key][0] = 'b.'.$val['0'];
            } else {
                $where[$key][0] = 'a.'.$val['0'];
            }
        }
    }
    /*$where_str = implode(' AND ',$where_str);
    if(!empty($where_str)){
        $where_str = 'WHERE  '.$where_str;
    }
    $sql_str = "SELECT a.*, b.iol_code, b.nickname, b.true_name, b.mother_tongue, b.first_language, (sum(duration_time)/(1000*60)) as total_time,DATE_FORMAT(a.create_time,'%Y-%m-%d') as _my_date FROM iol8_translator_online_time AS a LEFT JOIN iol8_translator.base_info AS b ON b.translator_id = a.translator_id  ".$where_str."  GROUP BY a.translator_id,_my_date ORDER BY a.id DESC";
    $list = DB::connection('report') -> select(DB::raw($sql_str));
    $list = array_map(function ($value) {//将对象转换成数组
        return (array)$value;
    }, $list);*/
    $list =  self::from('iol8_translator_online_time as a')->select(DB::raw($fields))->leftJoin('iol8_translator.base_info as b','b.translator_id','=','a.translator_id')-> where($where)->skip($offset)->take($pageSize) ->groupBy('a.translator_id') -> groupBy('_my_date')->orderBy('a.id','desc')->get();
    if(!empty($list)){
        $list = $list -> toArray();
        $languages = \App\Models\Language::getList();//语种
        foreach($list as $key => $val){
            if(!empty($val['mother_tongue'])){
                $val['mother_tongue'] = $languages[$val['mother_tongue']];
                $list[$key]['mother_tongue'] = (is_array($val['mother_tongue']) && !empty($val['mother_tongue']))?$languages[$list[$key]['mother_tongue']]['lang_name_zh']:'';
            }
            if(!empty($val['first_language'])){
                $val['first_language'] = $languages[$val['first_language']];
                $list[$key]['first_language'] = (is_array($val['first_language']) && !empty($val['first_language']))?$languages[$list[$key]['first_language']]['lang_name_zh']:'';
            }
        }
    }
    return $list;
}

//查询的条件封装
public static function queryFormat($request,$default = array()){
    $query = array();
    $start = $request->get('start');
    if($start){
        $query[] = ['create_time','>=',$start];
    }
    $end = $request->get('end');
    if($end){
        $query[] = ['create_time','<=',$end];
    }
    $translator_id = $request->get('translator_id');
    if($translator_id){
        $query[] = ['translator_id','like','%'.$translator_id.'%'];
    }
    $true_name = $request->get('true_name');//需联表查询的字段
    if($true_name){
        $query[] = ['true_name','like','%'.$true_name.'%'];
    }
    if(!empty($default)){
        $query = array_merge($query,$default);
    }
    return $query;
}