很多时候,数据库获取的信息并不是我们最终想要的,需要通过if进行处理。
where支持查询
having支持后查询(查询后的数据,再筛选)
代码如下:
if ($this->_post('dosearch','isset')) { // 搜索 if ($s_name = $this->_post('s_name','isset')) { $where['a.name'] = array('like','%'.$s_name.'%'); $this->assign('s_name',$s_name); } if ($s_category = $this->_post('s_category','isset')) { $where['a.category_id'] = $s_category; $this->assign('s_category',$s_category); } if ($s_status = $this->_post('s_status','isset')) { $having ='status ='.$s_status; // 只支持字符串 $this->assign('s_status',$s_status); } }
if
// 获取商铺 $subQuery = M('User')->where(array('agent_id'=>$this->agent_id,'status'=>1)) ->field('id') ->select(false); $where['a.user_id'] = array('exp','in '.$subQuery); $where['a.status'] = 1; $list = M()->table('sh_store a') ->join('sh_goods b on a.id = b.store_id') ->join('sh_category c on a.category_id = c.id') ->join('sh_mall_shop d on a.id = d.store_id and d.mall_id = '.$this->mall['id']) ->where($where) ->group('a.id') ->having($having) ->field('a.id as store_id,a.user_id,a.name as store_name,c.name as category_name,a.logo,count(b.id) as goods_count,if(d.id > 0,"1","2") as status') ->select(); $this->assign('list',$list);
sql原句如下:
SELECT a.id as store_id,a.user_id,a.name as store_name,c.name as category_name,a.logo,count(b.id) as goods_count,if(d.id > 0,"1","2") as status FROM sh_store a LEFT JOIN sh_goods b on a.id = b.store_id LEFT JOIN sh_category c on a.category_id = c.id LEFT JOIN sh_mall_shop d on a.id = d.store_id and d.mall_id = 9 WHERE ( a.category_id = '47' ) AND ( (a.user_id in ( SELECT `id` FROM `sh_user` WHERE ( `agent_id` = 13 ) AND ( `status` = 1 ) )) ) AND ( a.status = 1 ) GROUP BY a.id HAVING status =1
本文转自TBHacker博客园博客,原文链接:http://www.cnblogs.com/jiqing9006/p/5085216.html,如需转载请自行联系原作者