mysql if对数据进行处理 having对数据进行查询 thinkphp中的exp支持更复杂的where查询

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);
            }
        }

// 获取商铺
        $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);

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，如需转载请自行联系原作者