MySQL full-text search

Yii 2.0 General Discussions
1

Hi

First of all how can I use yii\db\migration to alter table and make some columns Full-Text index?

Secondly, please look at this code:




$query = new  \yii\db\Query();

$query->addSelect("MATCH(col1,col2) AGAINST (':search_query' IN NATURAL LANGUAGE MODE) AS score");

How can I dedicate bind param and value to :search_query like what we do in where() method (e.g. [’:search_query’ => value] ) or any other way to reduce attack risk?

I need use the query in ActiveDataprovider, so createCommand() might be useless.

I know sphinx and elasticsearch are better solution for full-text search but I need to do it with mysql, at the moment.

2

It seems Yii2 doesn’t support MySQL full-text search.

3 



public function up ()

{

	$this->execute('Any DDL SQL here');

}

No need to worry about database abstraction and portability and use the abstraction API here because full text indices and searches require vendor-specific syntax anyway.




$query->addParams([':search_query' => $searchQuery]);
4

It works. Thanks!

I searched for a solution free of database abstraction but I didn’t find execute() method.

It works , of course , but it is ignored as I add WHERE condition and this error is occurred:

The entire query is this:




$query->addSelect("MATCH(title,tag) AGAINST (':query_search' IN NATURAL LANGUAGE MODE) AS score")

        ->where('status=:status_published OR status=:status_finished AND reliable = 1',

            [

              ':status_finished'=>Poll::STATUS_FINISHED,

              ':status_published' => Poll::STATUS_PUBLISHED

            ]

        )

        ->addParams([':query_search' => $q ])

        ->limit(self::LIMIT_NUMBER)

        ->orderBy('score DESC');

I tried to put all params in addParams() including those are in where() but it didn’t make any difference.

I really appreciate you for helping me.

5

Yes, I forgot about ActiveDataProvider and its COUNT(*) query. Seems like you have to fetch the total count manually:




$query->where(

   'status=:status_published OR status=:status_finished AND reliable = 1',

   [        

   	':status_finished'=>Poll::STATUS_FINISHED,        

   	':status_published' => Poll::STATUS_PUBLISHED

   ]

);

$totalCount = $query->count();

$query->addSelect("MATCH(title,tag) AGAINST (':query_search' IN NATURAL LANGUAGE MODE) AS score")

	->addParams([':query_search' => $q ])

    ->limit(self::LIMIT_NUMBER)

    ->orderBy('score DESC');

$provider = new ActiveDataProvider([

        'query' => $query,

        'totalCount' => $totalCount,

]);
6

Thank you, it’s solved my problem

7

It didn’t fix the error




Invalid parameter number: number of bound variables does not match number of tokens

The SQL being executed was: SELECT MATCH(title, tag) AGAINST (''someSearchQuery'' IN NATURAL LANGUAGE MODE) AS score FROM `poll` WHERE status=1 OR status=3 AND reliable = 1 ORDER BY `score` DESC LIMIT 20

The error is about parameters and binding, isn’t it? As I know, it happens when number of parameters is not equal to number of bound values. When I remove the where() method from query everything start working again whether totalCount is there or not.

8

Well, I write here possible solutions for future visitors.

There is no way to bind some parameters to yii\db\Query::select() via addParams() and bind some other parameters in where() simultaneously for ActiveDataProvider because params of addParams() is ignored for some reason. All blends of addParams() and where()was tested.

What can we do?

  1. Code like those days in elementary school and insert user input into database query. For example:

$query->select("* , MATCH(title) AGAINST (’$userQuery’ IN NATURAL LANGUAGE MODE) AS score")

  1. Do not user where() while you need to bind parameters to select()

  2. Feel free and implement whole active query process and data provider proccess from scratch.

Good Luck

9

Full text search
Below describes a full text search on a related record, the widget column is showing the number of related records, and the search on that column filters on a field in the related records.

  1. create the full text index in mySQL
  2. Add the search field to the model:
public string $message;

and add to the rules:

    [['message'],'string'],
  1. Add the message field to the search model e.g.
public function rules()
    {
        return [
            ...
            [[..., 'message'], 'safe'],
        ];
    }
  1. Add the filter to the query for the dataProvider:
    $query->joinWith('issueMessages'); // this makes the count() in 5. below efficient, plus gets used below in andWhere if the $message filter is active
    $query->andFilterWhere(['id' => $this->id])
    ->andFilterWhere(['>=', 'last_active', $this->last_active ])
    ->andFilterWhere(['status' => $this->status])
    ->andFilterWhere(['like', 'subject', $this->subject])
    ->groupBy('issue.id'); // otherwise the gridview pagination mis-counts because of the joinWith
    if( $this->message ){    
        $query->andWhere("MATCH (issue_message.message) AGAINST ('" . $this->message . "' IN NATURAL LANGUAGE MODE)");
    }
  1. Display the filtered column:
GridView::widget([
...
[
        'label' => 'messages',
        'content' => function( $model, $key, $index, $column ){ return count($model->issueMessages);},
        'filter' => Html::input('text', 'IssueSearch[message]', '', ['class'=>'form-control']),
],