$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();
Yii使用 case when 来模糊排序查询
最新推荐文章于 2021-09-03 11:11:15 发布