Structured, dynamic attributes in ActiveRecord for SQL tables

Yii 2.0 General Discussions
1

UPDATE We have a new web site for the extension. It has a new README explaining its use with examples. New HTML docs are familiar-looking because they were built using yii2-apidoc. And the code has matured through use and debugging.

AND anyone using MariaDB’s Dynamic Columns may like the new Sequel Pro bundle that displays their content.

Both Maria 10 and PostgreSQL 9.4 can store recursively-structured objects in a blob column and allow references to object properties in queries, e.g. in SELECT and WHERE. It’s an attractive way to tackle the kind of problem that might suggest an EAV table, e.g. user profiles, client configurations, product descriptions.

I haven’t used PostgreSQL jsonb but I can say from experience that Maria’s Dynamic Column SQL functions are no fun to use. In any case, if I am using AR, I want to use the object idiom also for dynamic attributes. So I started writing an extension to provide it.

In a DynamicActiveRecord model you can set attributes that aren’t declared properties, aren’t columns, and have no magic setter method. Values may be scalar or array. They become dynamic attributes. They are serialized by the DBMS on save and restored on read. You can use dynamic attribute names in DynamicActiveQuery.

With vital help from djagya and CeBe (thank you!), yii2-dynamic-ar is ready for feedback from some more users. It’s in the extensions and in Github as tom–/dynamic-ar. You can find me in #yii on Freenode as tom[]. More contact options in the composer.json.

2

Maria cannot index Dynamic Columns. radoo’s comment on yii2-dynamic-ar ext page reminded me to mention it.

I raised my concerns about Dynamic Columns on the maria-discuss list in January. I got lots of helpful replies that encouraged me to proceed with yii2-dynamic-ar and actually use it in a real project.

3

i have a similar case, however let’s say for example i have an Appliance model, with a json column named specifications and a type field…

the type would dictate what kinds of attributes can be stored in the specifications field, for example a type=TV will have specifications such as resolution, diagonal size, contrast ratio etc…

preferably also that i could make $appliance->specification to be an instance of TvSpecification model, this is to be able to also dictate the kinds of validations i want to the json attributes (just like an ordinary model)

4

Interesting question, ezekielnoob. I checked that it works with these new unit tests.

It might be easier to understand your question and explain what happens with an example. The following is valid so long as [font="Courier New"]TvSpecs[/font] is-a [font="Courier New"]Model[/font] and [font="Courier New"]$product->specs[/font] is somehow writable.


    $product = new Product();

    $product->specs = new TvSpecs(['scenario' => 'insert']);

    $product->specs->load(Yii::$app->request->post());

    if (!$product->specs->validate()) {

        // process validation errors

    }

That’s normal Yii stuff. The only difference DynamicActiveRecord makes is that the ‘specs’ property does not need to be declared or defined in Product – as soon as you write to it, it exists (that’s the fundamental principle of Dynamic AR).

The next part is more fun:


    $product->save(false);

    $thatTv = Product::findOne($product->id);

    \yii\helpers\VarDumper::dump($thatTv->specs);

    // $thatTv->specs equals $product->specs->toArray()

Dynamic AR converts [font=“Courier New”]$product->specs[/font], your TvSpecs model instance, to array using [font=“Courier New”]ArrayableTrait::toArray()[/font] before writing [font=“Courier New”]$product[/font] to the DB. So when you read the Product model back, it has a ‘specs’ dynamic attribute with the fields from your TvSpecs model.

In your example you said you have a column attribute ‘type’ and you can use it to choose a model class, which you can instantiate and assign (massively if you like) its attributes from [font=“Courier New”]$product->specs[/font].

So, if I understand your question (do I?), the answer is a big fat [size="5"]yes[/size].