CODEWITHSUNDEEP
phpmysqlactiverecordyii2-advanced-app
mohammad fallah.rasoulnejad
mohammad fallah.rasoulnejad

Reputation: 829

Yii2 - active record query search for a number in a string field

i have a string field that contains a json string in my table. lets name that tbl_table

lets say it has these 3 records in it:

id    json
----------------
1     [123,45,1]
2     [23,4,5]
3     [7,8,9]

now I want to fetch records that contain a number in their json field (using active records in Yii2), so:

$res = Table::find()->where(['like','json','23'])->all();

this query returns these records: 

id    json
----------------
1     [123,45,1]
2     [23,4,5]

this is not actually a wrong result but what I want is to return only the record with id=2. how can I do this?

and so for ['like','json','5'] or ['like','json','4'] and so on? the numbers are similar but I want the exact number to be match.

and another question I want the last question answer with change in this part ['like','json',[4,5,6]]. in other words I want to pass an array to like operator and it automatically make an OR operation for each element. is it somehow possible? if not how can I iterate on array and make multiple Or_like conditions with last question functionality without multi query executions ?

Upvotes: 1

Views: 4075

Answers (1)

bxN5
bxN5

Reputation: 1430

use expression to use mysql functions and use JSON_CONTAINS function

use yii\db\Expression;
$expression = new Expression('JSON_CONTAINS(fied, value)');
$now = (new \yii\db\Query)->select($expression);

p.s json_contains begins from version MySQL 5.7

refs https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html http://www.yiiframework.com/doc-2.0/yii-db-expression.html

Upvotes: 5

Related Questions