Eav Pattern Db Tables (Activerecord)

Yii 2.0 General Discussions
1

Hi guys!

I’d love to see some inbuilt activerecord for handling EAV patterned tables.

It’s a pattern I just can’t get away from in my application (without making things incredibly messy). But it’s a real headache with the current straightforward activerecords. If I use any kind of widget, cgridview/listview most of the inbuilt functionalities won’t work (filter/sort etc…). If you need example of actually usage I’ll be more than glad to provide some. (another case is filtering by merging criterias that just plain out don’t work)

There exists some extentions but they really don’t solve any of these.

The idea would be to be able to have some active record that would (on a higher level) treat the EAV table like another table (or view) using the Entity as a requirement to build the "virtual" table and with the Attributes as columns. This would make it very transparent when using the widgets.

Searching within these models would be made easier as well as we could just straightforwardly find entries by column name and not EA combination.

As we all know EAV isn’t efficient, and there’s bound to be some overhead here. But people using these patterns do so knowing full well that they’re sacrificing performance for readability / ease of use. But sometimes with Yii as it is, it really isn’t all that “easy” to use

2

Just to illustrate (in case I’m not being clear).

Currently if you have an EAV table with article information as follows:


ARTICLE TABLE (Article 'article' model)

ID    article_name

5       "CPU"

6      "Cheese"

7       "CPU"


PROPERTY TABLE (Property 'props' model)

ID    entity(article ID)     attribute      value

1            5               "frequency"   "2.3 GHz"

2            5                "socket"     "LGA 1567"

3            6                "smell"       "gross"

4            6               "maturity"     "old"

1            7               "frequency"   "2.3 GHz"

If you had you relation between Article and Property you would have to set a "getPropertyValue($attribute)" method in your Article model that would return the value for a given property attribute then go (to get all cpus):




$criteria = new CDbCriteria(array('condition'=>`article_name` = "CPU"));

$dataProvider = new CActiveDataProvider('Article', array(

			'criteria'=>$criteria)

$this->widget('zii.widgets.grid.CGridView', array(

                     'dataProvider'=>$dataProvider,

                     'columns'=>array(

                               'id',

                               'article_name',

                               array(

                                     'header'=>'Frequency',

                                     'value'=>'$data->getPropertyValue("frequency")'

                               ),

                               array(

                                     'header'=>'Socket',

                                     'value'=>'$data->getPropertyValue("socket")'

                               ),

                           )

             );

The immediate issues with this are that you can not set the dataProvider sort attributes for these fields.

"article.props" has no meaning nore does "article.props.frequency"

The other main one is that if you want to filter all CPUs with a frequency of "2.3 GHz" and "LGA 1567" sockets then the following does not work as it obviously tries to find rows that match both which is impossible:




$criteria = new CDbCriteria(array('condition'=>`article_name` = "CPU"));


$frequency_filter_crit = new CDbCriteria(array('together'=>true,

                                               'with'=>array('props'=>array(

                                                                      'condition'=>'attribute = "frequency" AND value = "2.3 GHz"'))));


$criteria->mergeWith($frequency_filter_crit);


$socket_filter_crit = new CDbCriteria(array('together'=>true,

                                               'with'=>array('props'=>array(

                                                                      'condition'=>'attribute = "socket" AND value = "LGA 1567"'))));




$criteria->mergeWith($socket_filter_crit);


$dataProvider = new CActiveDataProvider('Article', array(

			'criteria'=>$criteria)

$this->widget('zii.widgets.grid.CGridView', array(

                     'dataProvider'=>$dataProvider,

                     'columns'=>array(

                               'id',

                               'article_name',

                               array(

                                     'header'=>'Frequency',

                                     'value'=>'$data->getPropertyValue("frequency")'

                               ),

                               array(

                                     'header'=>'Socket',

                                     'value'=>'$data->getPropertyValue("socket")'

                               ),

                           )

             );

Ideally there would be some simple level of abstraction that would allow us to make a Property model that would be defined in relations() and would return data as if the EAV table were in fact a normal Table with attributes as columns and values as rows (dynamically).

So if you were looking for a CPU you would obtain a Property table like:


ID(optional and redundant)    frequency     socket

5                             "2.3 GHz"   "LGA 1567"

7                             "2.3 GHz"      NULL

Likewise you could save in the same fashion with $article->props->frequency = “1.2 GHz” or even search by attribute etc… that way. But maybe that’s pushing it.

I don’t know if this level of abstraction is doable/notrecommended, etc. But I thought I would throw it out there.

3

I fully understand your pain here and some have to go this route although they know it is wrong (support of legacy systems etc., your boss if forcing you as he doesn’t know any better) but IMHO the Yii core should NEVER support a flawed idea such as EAV. It is basically raping every intend of a RDBMS and a hack that made sense when there were no document stores available (a la MongoDb or CouchDb). I know that systems like Magento are using it and that not everybody wants to use a document store but to me it is like adding support for job queuing on top of MySQL. Yes, one can do it, but there are reasons not to and good tools to solve this exact problem. Yii 2.0 will have support for document based databases, so this may actually solve your problem in a more elegant way but until then your best bet will be to fork an existing extension, adapting it to your needs and reshare it with the community. I am sure that there are people who would be interested but it shouldn’t be part of the core.

4

yeah that’s fair. I’ll see what can be done to push for mongo. But I doubt anything will budge until it’s part of Yii core (Yii2)

5

There is an extension here:

http://yiiext.github…vior/index.html

I don’t know how complete (or good) it is, but it’s better than nothing, right? :)

And thanks to Haensel for confirming my sketchy impression of what EAV is.

(Sorry, I’m out of positive votes for today ;p )

6

EAV is a database anti-pattern Link