laravel5.1 子查詢（Query_Builder）

laravel 5.1 子查詢執行個體

原生SQL:

select count(uid) as onl,date_format(time,'%H') as hour from `d_user_login201705` where `id` in (select min(id) as mid from `d_user_login201705` where `type` = '0' and `time` >= '2017-05-10 00:00:00' and `time` <= '2017-05-10 23:59:59' group by `uid`) group by `hour`

在laravel5.1  中應該怎麼書寫呢。。。   

參考文章   子查詢列子

源碼方法：點擊開啟連結         

$previous = DB::connection('log')                         ->table($table_prefix.$start_table)                         ->select(DB::raw("count(uid) as onl,date_format(time,'%H') as hour"))                                                                //方法外引用變數                         ->whereIn('id',function($query) use ($table_prefix,$start_table,$start_time,$end_time){                                 $song = DB::connection('log')                                         ->table($table_prefix.$start_table)                                         ->where('type','=','0')                                         ->where('time','>=',date('Y-m-d 00:00:00',$start_time))                                         ->where('time','<=',date('Y-m-d 23:59:59',$start_time))                                         ->select(DB::raw("min(id) as mid"))                                         ->groupBy('uid');                                 $song_str = $song->toSql(); //擷取原生sql 語句                                 //結果  select min(id) as mid from `d_user_login201705` where `type` = ? and `time` >= ? and `time` <= ? group by `uid`                                 $song_val = $song->getBindings();               //變數被?號代替的值（laravel 底層方法） 結果 Array(    [0] => 0    [1] => 2017-05-10 00:00:00    [2] => 2017-05-10 23:59:59)                                 $song_result =self::getStringReplace($song_val,$song_str) ;  //將擷取值與。替換                                 $query->select('mid')->from(DB::raw("($song_result) as tmp"));                                            })                         ->groupBy('hour')                         ->get();

from 字查詢案例

 /**     * 周活（登入表）     * @param Request $request     * @return $this     */    public function weekKeepView(Request $request){        set_time_limit(0);        $data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));        $data['end_time'] = $request->input('end_time',date('Y-m-d'));        $start_time = strtotime($data['start_time']);        $end_time  = strtotime($data['end_time']);        //讀取檔案        $file_name = 'weekData.json';        $week_data = Storage::disk('local')->exists($file_name) ? json_decode(Storage::get($file_name),true) : [];        $week_data_day = count($week_data)>0 ? array_column($week_data,'day') : [];        //迴圈查詢時間區間        for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){            $select_day[] =  intval(date('Ymd',$i));        }        //求出差集        $need_select_day = array_diff($select_day,$week_data_day);        //迴圈執行語句查詢        if(count($need_select_day)>0){            $res = [];            foreach($need_select_day as $key=>$val){                $self_time = strtotime($val);                $self_start_time = date('Ymd',$self_time-86400*6);                $self_end_time = date('Ymd',$self_time+86400);                if(substr($self_start_time,0,6)==substr($self_end_time,0,6)){                    $res[$key] = DB::connection('log')->table('d_user_login'.substr($val,0,6))                        ->select(DB::raw("count(distinct(uid)) as total"))                        ->where('type','=',0)                        ->whereBetween('time',[$self_start_time,$self_end_time])                        ->lists('total');                }else{                    $sql = DB::connection('log')->table('d_user_login'.substr($self_start_time,0,6))                           ->select('uid')->where('type','=',0)                           ->whereBetween('time',[$self_start_time,$self_end_time])                           ->union(                               DB::connection('log')->table('d_user_login'.substr($self_end_time,0,6))                                   ->select('uid')->where('type','=',0)                                   ->whereBetween('time',[$self_start_time,$self_end_time])                            );                    $list_sql = $sql->tosql();                    $list_val = $sql->getBindings();                    $sql_res =self::getStringReplace($list_val,$list_sql) ;                    $res[$key] = DB::connection('log')->table(DB::raw('('.$sql_res.') as tem'))->select(DB::raw('count(uid) as total'))->lists('total');                }                $res[$key]['total'] = $res[$key][0];                unset($res[$key][0]);                $res[$key]['day'] = $val;            };            $list = array_merge($res,$week_data);            $list = FunctionController::arr_sort($list,'SORT_DESC','day');            if(count($week_data_day)>0) Storage::delete($file_name);            //修改寫入檔案            Storage::disk('local')->put($file_name,json_encode($list));        }else{            //讀取檔案快取            //依據時間區間讀取            $start_time = date('Ymd',strtotime($data['start_time']));            $end_time  = date('Ymd',strtotime($data['end_time']));            foreach($week_data as $key=>$val){                if($val['day']==$start_time) $start = $key;                if($val['day']==$end_time) $end = $key;            }            $list = array_slice($week_data,$end,$start-$end+1);        }        return view('chart/keep/weekKeepView')->with('week_keep_data',$list)->with('data',$data);    }

 /**     * Query_toSql ? 號替換值     * @param $array query_getBindings 值     * @param $string query_tosql     * @return string     */    public function getStringReplace($array, $string){        $result = '';        $stringArray = explode("?", $string);        foreach($stringArray as $k=>$v){            if(isset($array[$k])){                $result .= $v."'".$array[$k]."'";            }else {                $result .= $v;            }        }        return $result;    }