0

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.

Dhiraj Gupta
  • 9,704
  • 8
  • 49
  • 54

2 Answers2

1

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"]);
csminb
  • 2,382
  • 1
  • 16
  • 27
0

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"]);
Ebrahim Poursadeqi
  • 1,776
  • 2
  • 17
  • 27
  • I tried this approach. I'm obviously using the above query within another, more complicated query. When I run the query, I get positional SQL parameter errors like "Parameter 3 was expected as integer, but you're sending a string". Note that I'm actually only sending two parameters in my query. – Dhiraj Gupta Jul 10 '17 at 15:48