记tp5多连接查询 放到一起 whereor 、find inset、sort等等 

 1          $user_id  = $this->user_id;
 2         $user = $this->user;        
 3         
 4         $cat_id = intval(input('cat_id'));
 5         $page = intval(input('page'));
 6         $goods_value = input('goods_value');
 7         $size = input('size',12);
 8         
 9         $price_start = input('price_start');
10         $price_end = input('price_end');
11         $sort = input('sort','desc');//升序 asc 降序desc
12         $sort_type = input('sort_type','goods_id'); //排序字段 综合sort 价格price 销量sale_num
13         
14         if($sort_type){
15             if($sort_type == 'new'){
16                 $sort_cloum = 'goods_id';
17             }elseif($sort_type == 'price'){
18                 $sort_cloum = 'cost_price';
19             }else{
20                 $sort_cloum = $sort_type;
21             }
22             
23             $order = [$sort_cloum => $sort];
24         }
25         
26         $where = array();
27         $wherein = array();
28         $whereor = array();
29         $whereorCat = array();
30         
31         $price_cloumn = 'cost_price';
32         if($user['discount'] == 0)$price_cloumn = 'shop_price';
33         // 按照会员计算实际价格
34         if($price_start > 0){
35             $price_start = Goods::getTruePriceSerch($user_id,$price_start);
36         }
37         if($price_end > 0){
38             $price_end =  Goods::getTruePriceSerch($user_id,$price_end);
39         }
40 
41         if($price_start > 0 && $price_end>0){
42             
43             $where[] =  [$price_cloumn,'between',"$price_start,$price_end"];          
44         }elseif($price_start>0){
45             
46             $where[] =  [$price_cloumn,'>=',$price_start];
47         }elseif($price_end>0){
48             
49             $where[] =  [$price_cloumn,'<=',$price_end];
50         }
51 
52         if($cat_id){
53             // 扩展分类查询 暂无子分类
54             $gids = Db::name('goods_cat')->where(['cat_id'=>$cat_id])->column('goods_id');
55             if($gids){
56                 $gids_str = implode(',',$gids);
57                 $whereorCat[]=['goods_id','in',$gids_str];
58                 $whereorCat[]=['cat_id','=',$cat_id];
59             }else{
60                 //子分类查询
61                 $sonIdArr = Db::name('category')->where(['parent_id'=>$cat_id])->column('cat_id');
62                 if($sonIdArr){
63                     $sonIdArr[] = $cat_id;
64                     $sonIdStr = implode(',',$sonIdArr);
65                 }
66                 if($sonIdStr){
67                     $wherein = "cat_id in (".$sonIdStr.")";
68                 }else{
69                     $where[]=['cat_id','=',$cat_id];
70                 }                
71                 
72             }
73         }        
74         
75         if($goods_value){
76             //商品模糊搜索
77             // 品牌搜索
78             $brand_id = Db::name('brand')->where(['brand_name'=>$goods_value])->value('brand_id');
79             // var_dump($brand_id);
80             if($brand_id){
81                  $whereor[]=['goods_name|goods_sn|keywords','like','%'.$goods_value.'%'];
82                  $whereor[]=['brand_id','=',$brand_id];
83             }else{
84                   $where[]=['goods_name|goods_sn|keywords','like','%'.$goods_value.'%'];
85                 
86             }
87         }
88         $goodsList = GoodsLogic::getGoodsList($where,$order,$user_id,$wherein,$whereor,$whereorCat,$size);

以上为条件

    public static function getGoodsList($where,$sort='',$user_id,$wherein='',$whereor='',$whereorCat='',$size){
        $where[]=['is_on_sale','=',1];
        $list = Db::name('goods')->where($where)->where($wherein)->where(function ($query) use($whereor){$query->whereor($whereor);})->where(function ($query) use($whereorCat){$query->whereor($whereorCat);})->order($sort)->paginate($size)->each(function($item,$key)use($user_id){
            $item['charge_fee'] = round(Goods::getChargeFee($user_id,$item['house_id']),2);
            $item['shop_price'] = round(Goods::getTruePrice($user_id,$item['goods_id']),2);
            $item['stock'] = getStock($item['goods_sn']);
        
            if($user_id > 0){
                $item['collected'] = 0;
                $res = Db::name('goods_collect')->where(['user_id'=>$user_id,'goods_id'=>$item['goods_id']])->find();
                if($res){
                    $item['collected'] = 1;
                }
            }
            return $item;
        });
        //var_dump(Db::getLastSql());die;
        return $list;
    }

以上为查询
打印实际sql
SELECT * FROM `pl_goods` WHERE `is_on_sale` = 1 AND ( ( `goods_name` LIKE '%花王%' OR `goods_sn` LIKE '%花王%' OR `keywords` LIKE '%花王%' ) OR `brand_id` = 1 ) AND ( `goods_id` IN (220) OR `cat_id` = 122 ) ORDER BY `goods_id` DESC LIMIT 0,20

 

posted @ 2020-11-11 14:37  Liiu  阅读(230)  评论(0)    收藏  举报