yii FULLTEXT index matching query

Question

I want to creat a sql query in yii framework that look like this:

mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');

i try this but it's not working:

    $dataProvider = new CActiveDataProvider('data', array(
            'criteria'=>array(
                'condition' => 'MATCH (title,body) AGAINST ('Security implications of running MySQL as root') AS score', 
WHERE MATCH (title,body) AGAINST ('Security implications of running MySQL as root')
                'limit' => '20',            ),
            'pagination' => false
        ));

Try model()->findAllBySql("Query") – Harikrishnan Nov 20 '13 at 05:09 — Harikrishnan, Nov 20 '13 at 05:09

score 2 · Answer 1 · answered Jan 09 '14 at 07:51

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#condition-detail

This refers to the WHERE clause in an SQL statement

So there should be only MATCH (title,body) AGAINST ('Security implications of running MySQL as root')

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#select-detail

This refers to the SELECT clause in an SQL statement

Here your place for id, body, MATCH (title,body) AGAINST ('Security implications of running MySQL as root') AS score

score 2 · Answer 2 · answered Aug 25 '17 at 04:58

First of all you need to execute the ALTER query for search column.

 ALTER TABLE item ADD FULLTEXT(title,description,location);

Query to search

 $query = \common\models\Item::find()
             ->where(['is_active'=>'1']);
 $query->andWhere("MATCH(title,description,location) AGAINST 
 ('$searchKeywords' IN BOOLEAN MODE)");

yii FULLTEXT index matching query

2 Answers2