How use MATCH AGAINST where condition in nested condition array?

Yii 2.0 General Discussions
1

I have this ActiveRecord where condition with multiple conditions and within this nested array of rules I need to add a MATCH AGAINST fulltext condition. I can’t seem to figure our how I can add this within my current structure - or if its even possible/supported by the Yii2 AR builder?

This is my code:


// Query

$query = UserRecord::find();


// Start building where statement

$where = [];


// Where statements


// OR

$or = ['or'];

$or[] = ['=', 'users.id', $param];

$or[] = ['LIKE', 'users.address', $param];

$or[] = ['LIKE', 'users.zip', $param];

$or[] = ['LIKE', 'users.city', $param];

$where[] = $or;


// AND

$where[] = ['=', 'users.deleted', UserRecord::STATUS_NOT_DELETED];

$where[] = ['>=', 'DATE_FORMAT(users.dateCreated, \'%Y%m%d\')', DateHelper::formatDate($param, "Ymd")];


// Merge and add condition

$query->where(array_merge(['and'], $where));

I now need to add this line to the OR statements above:


MATCH(users.firstname, users.lastname) AGAINST(\''.$param.'\' IN BOOLEAN MODE)

So I get the SQL like… (users.email = X) OR (users.address = X) OR (MATCH(users.firstname, users.lastname) AGAINST()…

Is this even possible?

Thanks

2

Hi Naboovalley,

So you are using the "operator" format of "or" to construct the condition. You could simply include your "string" format of condition using "match" as one of the operands of "or".




// OR

$or = ['or'];

$or[] = ['=', 'users.id', $param];

$or[] = ['LIKE', 'users.address', $param];

$or[] = ['LIKE', 'users.zip', $param];

$or[] = ['LIKE', 'users.city', $param];

$or[] = 'MATCH(users.firstname, users.lastname) AGAINST ...';

$where[] = $or;

Please take a look at the following section of the guide:

Guide > Query Builder > where() (https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#where)

BTW,

This is not a safe way of constructing a SQL statement. It is vulnerable to a SQL injection attack. Please consider using a prepared statement and a parameter binding.

3

Thank you very much @softark. I was expecting it to always needed to be wrapped within its own sub-array… stupid.

Yeah I’ll add the binding to production code. Thanks for pointing that out.