laravel5.1 subquery (Query_builder)

Laravel 5.1 Sub-query instances

Native sql:

Select COUNT (UID) as Onl,date_format (time, '%H ') as hour from ' d_user_login201705 ' where ' id ' in (select min (id) as Mid fro M ' d_user_login201705 ' where ' type ' = ' 0 ' and ' time ' >= ' 2017-05-10 00:00:00 ' and ' time ' <= ' 2017-05-10 23:59:59 ' gr OUP by ' UID ') group BY ' Hour '

How to write in laravel5.1 ...

Reference Article Sub-query

Source method: Click to open the link

$previous = db::connection (' log ')->table ($table _prefix. $start _table)
                                                                ->select (Db::raw ("Count (UID) as Onl,date_format (time, '%H ') as Hour")) The method references the variable->wherein (' id ', function ($query) use ($table _prefix, $start _table, $sta
                                         Rt_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))->selec
                                 T (Db::raw ("min (id) as mid"))->groupby (' uid '); $song _str = $songTosql (); Get native SQL statements//Results select min (id) as mid from ' d_user_login201705 ' where ' type ' =? and ' Time ' >=? and ' Time ' <=? 
              GROUP BY ' uid ' $song _val = $song->getbindings ();
                                 The value of the variable is substituted by the number (Laravel underlying method) result 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); Gets the value associated with the.
                                            Replace $query->select (' mid ')->from (Db::raw ("($song _result) as TMP"); })->groupby (' Hour ')->get ();

From Word query case

 /** * Zhou Wu (login form) * @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 ']);
        Read file $file _name = ' Weekdata.json '; $week _data = Storage::d ISK (' local ')->exists ($file _name)?
        Json_decode (Storage::get ($file _name), True): []; $week _data_day = count ($week _data) >0?
        Array_column ($week _data, ' Day '): []; Loop query time interval for ($i = $start _time, $select _day=[]; $i <= $end _time; $i +=86400) {$select _day[] = intval (date
        (' Ymd ', $i));
        }//Travel set $need _select_day = Array_diff ($select _day, $week _data_day);
        Loop Execution Statement Query 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 ')->t
                        Able (' 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_ti
                Me])->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 _star T_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);
        Modified Write file Storage::d ISK (' local ')->put ($file _name,json_encode ($list));
            }else{//Read file cache//read by time interval $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 # Replacement Value
     * @param $array query_getbindings value
     * @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;
    }