There is a base Projects .

It has fields: id , name , partners , public .

The partners field is a string and contains data in the following form ["22","46","5","42","34","37","36"] , these data are recorded into the database through a multi-select in the admin panel.

I make filters on the frontend and there was a problem with selecting data by one of the values ​​of the partners field, you need to make such a request:

 $model = Project::find()->where(['public'=>1]) ->andWhere(['partners', '42']) ->orderBy('name DESC')->all();

In this form, the query naturally does not go through, it works through LIKE : ->andWhere(['LIKE', 'partners', '42'] , but if the values ​​are unambiguous, the sample will be incorrect.

Tell me how to properly select a sample of one of the values ​​of this field Perhaps the data of the partners field should be passed through json_decode , but how should this be linked to the database request?

  • Writing multiple values ​​in one field, you break the normal forms of the database and add yourself problems. - fens
  • And to solve the problem, dig in the direction of json processing in mysql in the latest versions like something flashed - fens
  • @fens, and how best to record data from the multi-select, for each such field you need to create a separate table and bind the data of this field through relations? - MasterAlex
  • Logically, yes, but since you have such a question, then you have a lot of such fields, then try the JSON_CONTAINS function available in new versions of MySQL - fens
  • @fens, with MySQL, I do not have such a close relationship, so I still found the option via json_decode , below described both of the resulting options - MasterAlex

2 answers 2

Not very beautiful, but quite workable option:

 $partnerId = 42; $model = Project::find()->where(['public'=>1]) ->andWhere("(partners LIKE '[\"" . $partnerId . "\",%' OR partners LIKE '%,\"" . $partnerId . "\",%' OR partners LIKE '%,\"" . $partnerId . "\"]')") ->orderBy('name DESC')->all();
  • Yes, thanks for the idea, only this option does not work very well, and if you do this, then it’s quite: $partnerId = 42; $partnerId = '"' . $partnerId . '"';$model = Project::find()->where(['public'=>1])->andWhere(['LIKE', 'partners', $partnerId]) $partnerId = 42; $partnerId = '"' . $partnerId . '"';$model = Project::find()->where(['public'=>1])->andWhere(['LIKE', 'partners', $partnerId]) . That is, I take a variable, wrap its value in quotes and search for profit! - MasterAlex

The result was 2 solutions, in my opinion not so flexible, but both work:

The first solution is parsing the field via json_decode :

 $projectId = 6; $model = Project::find() ->where(['public'=>1]) ->orderBy('date_end DESC')->all(); $projects = []; foreach($model as $project) { $partners = json_decode($project->partners); foreach ($partners as $partner) { if ($partner == $partnerId) { $projects[] = $project->attributes; } } }

The second solution is adding quotes for more strictly searching through LIKE (thanks @ P.Fateev for the idea):

 $partnerId = 6; // Значение приходит из реквеста, понятное дело, что этой строчки нет в итоге $partnerId = '"' . $partnerId . '"'; $model = Project::find() ->where(['public'=>1]) ->andWhere(['LIKE', 'partners', $partnerId]) ->orderBy('date_end DESC')->all();

The first may be correct, but stopped at the second solution, because there are several such parameters and so far there are no bugs, if there are any replacements with the first one, which works more strictly and compares the value.