Yii使用 case when 来模糊排序查询

Yii使用 case when 来模糊排序查询

$search_arr=['沙发','床','现代轻奢','新中式'];//越排在前面得关键词，优先模糊排序
 $where=[];
 $order_by="";
 $query = Goods::find()->alias('g')->where([

            'g.status' => 1,

            'g.is_delete' => 0,

            'g.type' => get_plugin_type(),

            'g.is_clear_sale'=>$is_clear_sale,





        ])->leftJoin(['m' => Mch::tableName()], 'm.id=g.mch_id')

            ->andWhere([

                'or',

                ['g.mch_id' => 0],

                ['m.is_delete' => 0]

            ]);
foreach ($search_arr as $k2=>$v2){
                
                 $kk = $k2+1;
                 if($k2==0){
                    $where[$k2]="or";
                    $where[$kk]=['like','g.name',$v2];
                    $order_by='case when `g`.`name` like \'%'.$v2.'%\' then '.$kk;
                 }
                 else{
                    $where[$kk]=['like','g.name',$v2];
                    $order_by.=' when `g`.`name` like \'%'.$v2.'%\' then '.$kk;
                 }

                
               }


 $order_by.=' end';
 $query->orderBy([$order_by => SORT_ASC]);
 $query->andwhere($where);
 $count = $query->count();
 $pagination = new Pagination(['totalCount' => $count, 'pageSize' => $this->limit, 'page' => $this->page - 1]);

 $list = $query->select('g.id,g.name,g.price,g.petao_introdu,g.original_price,g.cover_pic,gn.num,g.virtual_sales,g.unit,g.is_negotiable')

            ->limit($pagination->limit)

            ->offset($pagination->offset)

            ->asArray()->groupBy('g.id')->all();
————————————————
版权声明：本文为CSDN博主「码农PHP666」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_45472548/article/details/114402417
``[添加链接描述](https://blog.csdn.net/weixin_45472548/article/details/114402417)`