模糊条件查询:
$sql['user_login|user_nickname|user_email|mobile'] = ['like', "%$keyword%"];
//表示字段user、login|user、nickname|user、email|mobile 中有$keyword值的数据
$list = $usersQuery -> whereOr($sql) -> order("create_time DESC")
-> paginate(10,false,['query' => request()->param()]); //查询出的数据
//也可以这样写:
$listr = Db::name('user')->whereOr('user_login|user_nickname|user_email|mobile','like',"%$keyword%")
->paginate(10,false,['query' => request()->param()]);
主要是->whereOr()不能写错。
查询条件为数组:
$arr = [123,456];
$map['user_id'] = ['in',$arr];
Db::name('user') -> where($map) -> select();
//也可以写成这样:
Db::name('user') -> where('user_id','in',$arr) -> select();
//关于日期段
$map['change_time'] = [['>',$qians],['<',$hous]];
Db::name('user') -> where($map) -> select();
两个条件为and关系
实例:
public function withdrawalss() {
$withdrawalsModel = Db::name('plugin_recharge');
$status = $this->request->param('status');
$user_id = $this->request->param('uid');
$keyword = $this->request->param('keyword');
$riqi = $this->request->param('riqi');
$map = [];
//判断用户id
if(!empty($user_id)){
$map['user_id'] = $user_id;
}
//判断状态
if($status!=0){
if($status==1){
$map['status']=0;
}
if($status==2){
$map['status']=1;
}
if($status==3){
$map['status']=2;
}
}
//多条件模糊查询
if (!empty($keyword)) {
$listr = Db::name('user')->whereOr('user_login|user_nickname|user_email|mobile','like',"%$keyword%")->paginate(10,false,['query' => request()->param()]);
foreach ($listr as $k => $v) {
$arr= $v['id'];
}
$map['user_id'] = ['in',$arr];
}
//日期范围查询 用的插件 laydate.js 截取日期段:如:2018-11-21 - 2018-11-23
if (!empty($riqi)) {
$sj = explode('-',$riqi);
for ($i = 0;$i<3;$i++) {
$qian[] = array_shift($sj);
}
$hou = $sj;
$qians = strtotime(join('-',$qian));
$hous = strtotime(join('-',$hou));
$map['change_time'] = [['>',$qians],['<',$hous]]; //这两个条件为and关系
}
//数据分配
$list = $withdrawalsModel->alias('sr')
->join('user s','s.id = sr.user_id')
->field('sr.*,s.user_nickname')
->where("isggm=0")
->where($map)
->order('sr.id desc')
->paginate(20,false,['query' => request()->param()]);
$sum = $withdrawalsModel->alias('sr')
->join('user s','s.id = sr.user_id')
->where($map)
->where("isggm=0")
->count();
$this->assign('sum',$sum);
$this->assign('list',$list);
$this->assign('status',$status);
$this->assign('page', $list->render());
return $this->fetch('yuechong');
}