TP5 数据查询

//某个值是否在某个数组内查询   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;
});