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