- 子查询临时表再次过滤(如多条记录,要拿多条记录最大的时间去判断是否在范围内)
$result = $result->select([
"{$o}.customer_user_id",
"st.max_learn_progress", "st.is_finish",
'c.nickname', 'c.name_remark',
"{$o}.id as order_right_id",
DB::raw('MAX(bht_re.enter_webcast_last_time) as enter_webcast_last_time'),
's.staff_user_id',
])->orderBy("{$o}.id", 'desc')
->groupBy("{$o}.customer_user_id");
$result1 = DB::query()
->fromSub(function ($query) use ($result) {
$query->select('*')->from(DB::raw("({$result->toSql()}) as u"));
foreach ($result->getBindings() as $binding) {//toSql无法带入传入的过滤值,手动绑定值
$query->addBinding($binding);
}
}, 'u');
if(!empty($params['last_enter_start_time']) && !empty($params['last_enter_end_time'])) {
$result1 = $result1->whereBetween('enter_webcast_last_time',[$params['last_enter_start_time'],$params['last_enter_end_time']]);
}
- whereExists 方法适用于需要根据其他表的数据来过滤主查询的结果,只保留与子查询匹配的记录。
$result = $result->whereExists(function ($query) use ($rightTagIds) {
$query->select(DB::raw(1))
->from('third_goods_right_relate_tag')
->whereIn('third_goods_right_relate_tag.tag_id', $rightTagIds)
->whereRaw('bht_third_goods_right_relate_tag.goods_right_id = bht_third_order_rights.right_id');
});
- leftjoin闭包(闭包where相当于把连表的数据通过where过滤一遍,再左连接。where条件不闭包的话就相当于join了)
->leftJoin($p_log, function (Builder $query) use ($p_log, $log, $p_log_type) {
$query->on("{$p_log}.order_right_id", '=', "{$log}.order_right_id")
->whereIn("{$p_log}.type", $p_log_type);
})
- id => name分组
$staffUsers = UserOrgStaff::select(['user_id', 'nickname'])
->where(['org_id' => $orgId])
->pluck('nickname', 'user_id')
->toArray();
- 根据某个key组成对象
->keyBy('user_id')->toArray();
- 取前缀及if语句
$tp = env('DB_PREFIX');
$r->selectRaw("IF({$tp}qw_channel_code.type=1,{$tp}qr.qr_code_url,{$tp}sc.code_pic_url) as code_pic_url")
- group by的时候也可以分页,记得by id,by其它的可能导致顺序错乱
SELECT *
FROM `bht_third_order_rights`
LEFT JOIN `bht_user_org_customer`
ON `bht_third_order_rights`.`customer_user_id` = `bht_user_org_customer`.`user_id`
AND `bht_user_org_customer`.`org_id` = 9
WHERE `bht_third_order_rights`.`org_id` = 9
GROUP BY `bht_third_order_rights`.`id`
ORDER BY `bht_third_order_rights`.`id` DESC
LIMIT 20;
- 其它
mysql中,and a = 1 或者 a != 1,这种条件判断,会过滤null的值,也就是说,a != 1,不会返回为null的数据,虽然null != 1
//控制台调试
php bin/hyperf.php command:FunctionTest --name=test1
$a = null;//null或者''或者[]都不会报错
var_dump($a['a']['b'] ?? 1);//不会报错,并且返回为1
null ?? 1 = 1 (??只有当前面的值为null时,才取后面的值)
null,0,false,'',[] ?: 1 = 1 (?:为三元运算符的简写方式)
if(null,0,'0',false,'',[]) 或者 !empty 这些值都为false
退出php服务:按ctrl+\(反斜杠)
重启项目:
php bin/hyperf.php tmg:start -dc
查看进程占用情况:sudo lsof -i :9801
终止进程:sudo kill -9 1234
function gosql(sql) {
// 发送 POST 请求
fetch('https://api.bhtnet.com/api/org/system/runSql', {
method: 'POST',
headers: {
"bhtapitoken": "",
'Content-Type': 'application/json;charset=UTF-8' // 指定请求正文的类型为 JSON
},
body: JSON.stringify({db:'bhtdbp',sql:sql}) // 将参数转换为 JSON 字符串
})
.then(response => response.json())
.then(data => {
console.log('请求成功', data);
})
.catch(error => {
console.error('请求失败', error);
});
}
var aaaindex = 0;
var aaa = [];
function loop() {
if (aaaindex < aaa.length) {
gosql(aaa[aaaindex]);
aaaindex++;
setTimeout(loop, 1000);
}
}
loop();