How to CRUD Activerecord Model with JSON field in Mysql

Yii 2.0 General Discussions
1

Hello,

I’m trying to implement a EAV model using json since mysql and yii (2.14 and up) support this, but I wonder what is the proper way to do this :

I have a Simple table that would define what extra ‘field’ are wanted, called ‘extraFieldDef’ :
extraFieldDef : id, name

and a entry as :
user : id, login(varchar),…, customFields (json)

Thank you

2

So what is the actual question?

3

With $model->myJsonFieldsValues

What code should goes into my model definition as it extend from AR ? How a SearchModel would look like to allow filters on the Json entities ?

Thank you

4

You mean your model’s properties?
Well, you could do an extra model class like MyJsonFields, where the properities are your JSON keys.
So your $model->myJsonFieldsValues isn’t a string holding the JSON data, but represents an instance of your MyJsonFields class.
After loading into it, you could access the JSON fields like $model->myJsonFieldsValues->id

In SearchModel you could filter directly on the JSON string, or decode the value in an array and do the searching there.

1 Like
5

Accessing $model->myJsonFieldsValues->id without any modification wouldn’t work, I mean the field is just a ‘safe’ on the model, how/when would it be coded/decoded ?

Would you please explain this part :
#################
So your $model->myJsonFieldsValues isn’t a string holding the JSON data, but represents an instance of your MyJsonFields class.
##################

Thank you for your reply, I’m really counting on it

6

For e.g. your JSON stored data in column “myjsondata_column” in table “exampledata” looks like this:

{“extra_id”:“1”, “name”:“yii2”}

You have your Model class to that table:

/**
* @property int $id 
* @property int $myjsondata_column
*/
class Exampledata extends ActiveRecord {
  /* @var MyJsonFields $myJsonFieldsValues */
  public $myJsonFieldsValues;

  public static function tableName(){
    return 'exampledata';
  }
  /** Pseudocode of injecting the data, called from some place like init() **/
  private function injectJsonData(){
      $jsondata = Json::decode( $this->myjsondata_column );
      $this->myJsonFieldsValues = new MyJsonFields();
      $this->myJsonFieldsValues->id = $jsondata["id"];
      $this->myJsonFieldsValues->name = $jsondata["name"];
  }
}

You make an extra Model class for your JSON data:

class MyJsonFields {
  /* @var string $extra_id */
  public $extra_id;
  /* @var string $name */
  public $name;
}

That model is a member variable in your Exampledata class.
Just an idea.

1 Like
7

Thank you so much for the code example.

This works on the assumption that we know the keys in the json string, but I don’t have that.

ex
ex2964×2480 984 KB

This is my database, and I have a direct mapped Models to the three tables, the idea is to check for the “custom fields list” from the customdata Table, and only get these entry based on the names (these are the dynamic keys), to decode the json from the ‘customdata’ attribute in the member table.

class Member extends \yii\db\ActiveRecord {
//I have this as your code suggestion :
public function init()
{
injectJsonData();
}
private function injectJsonData(){
$jsondata = Json::decode( $this->customdata );
$this->myJsonFieldsValues = new MyJsonFields();
$this->myJsonFieldsValues->id = $jsondata[“id”];
$this->myJsonFieldsValues->name = $jsondata[“name”];
}
…
}

How to make that json_decode to attribute affectation works dynamically ?

Thank you

8

Look at what JSON::decode() is doing.
It stores the decoded JSON string in an associative array.
You can iterate through it to dynamically assign.

Does this have any particular reason, why your tables are structured like this or why this have to be solved this way?
The JSON data in your member.customdata column has mostly totally different keys?