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