TP5 数据查询

港控/mmm° 2021-08-29 02:48 608阅读 0赞

//某个值是否在某个数组内查询 where('id','in', $ids)
    $ids = '1,2,3,4,5';
    $list= Db::name('product') -> where('id','in', $ids) -> select();
    
    //随机查询 orderRaw('rand()')
    $list= Db::name('product') -> where('id','in', $ids)->orderRaw('rand()')->limit(4) -> select();
    
    //指定数量查询 limit(4)
    $list= Db::name('product') -> where('id','in', $ids)->limit(4) -> select();
    
    //从某一条数据开始(从第4条开始查询，查询4条)
    $list= Db::name('product') -> where('id','in', $ids)->limit('4,4') -> select();
    
    //取值查询 value()
    $value= Db::name('product') -> where('id','in', $ids)->value("name");
    
    //取列查询 column()
    $names= Db::name('product') -> where('id','in', $ids)->column("name");
    
    //模糊查询
    $where = [
    	['name', 'like', "%".$_POST['keyword']."%"],
    ];
    $lists= Db::name('product')-> where($where)->select();
    
    //聚合查询 [双where] -> where($where1) ->where($where)
    $where = [];
    $where1= [];
    if(!empty($_POST['title'])){ 
    	$where = [['title','like',"%{ $_POST['title']}%"]];
    }
    if(!empty($_POST['cid'])){ 
    	$where1['cid'] = $_POST['cid'];
    }
    
    if(!empty($_POST['did'])){ 
    	$where1['did'] = $_POST['did'];
    }
    $this->renzhengs = Db::name('renzheng')-> where($where1) ->where($where)->paginate(10)->each(function($item, $key){ 
    	$item['addtime'] = date('Y/m/d',$item['addtime']);
    	return $item;
    });
    
    //一个字段 大于另一个 字段 查询
    $where['is_show'] = 1; 
    $lists = Db::name('user')->whereExp('money','>= inter')->where($where)->select();
    
    //区间查询 在时间范围内查询
    $where['starttime'] = array('elt',time());
    $where['endtime'] = array('egt',time());
    $list = Db::name('st_coupon_list')->where($where)->order('addtime desc')->limit(($page-1)*$size, $size)->select();
    
    //或者查询(双条件查询) whereOr
    $lists = Db::name('user')->where($where)->whereOr($whereOr)->select();
    
    //join两表联查
    $where['t1.status'] = 1; 
    $list = db('feedback')->where($where)->order('addtime desc')->
    alias('t1')->
    join('hd_user t2','t1.uid = t2.id','LEFT')->
    field('t1.*,t2.nickname,t2.avatar')->
    paginate($this->getPageSize(),false,['query' => $this->getRequest()->param()]);
    
    
    //FIND_IN_SET查询 teacher_id 这个字段包含的值："1,2,3"
    $teacher_id = 1;
    $where[] = ['exp',Db::raw('FIND_IN_SET('.$teacher_id.',teacher_id)')];
    $where['status'] = 1;
    $list = Db::name('course')->where($where)->select();
    
    //查询 关联表查数据
    $list = db('p_question')->paginate($this->getPageSize(),false,['query' => $this->getRequest()->param()])->each(function($item, $key){ 
    	$item['subject'] = db('p_question_list')-> where(array( 'id' => $item['subject_id'])) -> find();
    	return $item;
    });