CODEWITHSUNDEEP
phpmysqlyiiyii2
Dhiraj Gupta
Dhiraj Gupta

Reputation: 10504

How to do case sensitive where() 'in' match in yii2 with mysql database

I've got a hashtags table in my mysql db, with a string(512) column called tag. I want to do a case sensitive search using Yii2's ActiveQuery, like this:

$expTags = ['one','two','three'];
Hashtag::find()->where(["IN","tag",$expTags])->select(["id"]);

In mysql, this usually means prefixing BINARY outside every value, or prefixing the column name. I tried both, I even tried "IN BINARY", nothing worked.

How to do this? I need case sensitive search, and I don't want to modify the table / column collation just to do this.

Upvotes: 0

Views: 2113

Answers (2)

csminb
csminb

Reputation: 2382

previous answer here got it pretty close:

$expTags = ['one','two','three'];
Hashtag::find()->where(["BINARY IN","tag",$expTags])->select(["id"]);

BINARY IN (used as operator) causes a syntax error (for mysql at least)

Hashtag::find()->where('BINARY tag in(:tag)', ['tag'=> $expTags])->select(["id"]);

and this hits the pdo problem of binding an array to a single param

this will work however:

Hashtag::find()->where(['BINARY(`tag`)' => $expTags])->select(["id"]);

Upvotes: 1

Ebrahim Poursadeqi
Ebrahim Poursadeqi

Reputation: 1816

you can try this:

$expTags = ['one','two','three'];
Hashtag::find()->where(["BINARY IN","tag",$expTags])->select(["id"]);

or

Hashtag::find()->where('BINARY tag in(:tag)', ['tag'=> $expTags])->select(["id"]);

Upvotes: 0

Related Questions