2019-08-27 11:43:17 2318次浏览 3条回答 0 悬赏 15 金钱
$w='xxxx';
echo  WeixinCourse::find()
    ->select('id')
    ->andWhere(['or', ['like', 'name', $w], ['like', 'description', $w]])
    ->orderBy("if(instr(name,'{$w}') >0,1,0) desc,id desc")
    ->createCommand()->getRawSql();die;

我想要的

SELECT `id` FROM `weixin_course` WHERE (`status`=1) AND ((`name` LIKE '%xxxx%') OR (`description` LIKE '%xxxx%')) ORDER BY if(instr(name, 'xxxx') >0, 1, 0) DESC, `id` DESC

但实际是

SELECT `id` FROM `weixin_course` WHERE (`status`=1) AND ((`name` LIKE '%xxxx%') OR (`description` LIKE '%xxxx%')) ORDER BY if(instr(name, `'xxxx') >0`, `1`, `0)` DESC, `id` DESC

就是order 排序那里 会多转义符号 ` 怎么解决

  • 回答于 2019-08-27 19:03 举报

    一般我都是把内置函数放到select的字段里面,在对结果进行处理。可以这样试试

    $w = 'xxxx';
            echo WeixinCourse::find()
                ->select(["id","if(instr(username,'{$w}') >0,1,0) as temp"])
                ->andWhere(['or', ['like', 'name', $w], ['like', 'description', $w]])
                ->orderBy("temp desc,id desc")
                ->createCommand()->getRawSql();
            die;
    
  • 回答于 2019-08-30 11:55 举报

    use yii\db\Expression;

    原来的
    `$w='xxxx';
    echo WeixinCourse::find()

    ->select('id')
    ->andWhere(['or', ['like', 'name', $w], ['like', 'description', $w]])
    ->orderBy("if(instr(name,'{$w}') >0,1,0) desc,id desc")
    ->createCommand()->getRawSql();die;`
    
    

    改为
    `$w='xxxx';
    echo WeixinCourse::find()

    ->select('id')
    ->andWhere(['or', ['like', 'name', $w], ['like', 'description', $w]])
    ->orderBy(new Expression("if(instr(name,'{$w}') >0,1,0) desc,id desc"))
    ->createCommand()->getRawSql();die;`
    
  • 回答于 2019-09-16 18:08 举报

    仅供参考:
    WeixinCourse::find()
    ->where(['LIKE', 'name', $xxxx.'%', false])//%这样放,可以使name索引(设置了索引的话)生效
    ->andWhere(['status'=>1])
    ->orWhere(['LIKE', 'description', $xxxxX.'%', false])//%这样放,可以使description索引(设置了索引的话)生效
    ->orderBy("if(instr(name,'{$w}') >0,1,0) desc,id desc")
    ->all();

您需要登录后才可以回答。登录 | 立即注册
Mingxin
实习生

Mingxin

注册时间:2018-08-23
最后登录:2019-09-02
在线时长:1小时13分
  • 粉丝0
  • 金钱0
  • 威望0
  • 积分10

热门问题