Yii Framework Forum: Eav Pattern Db Tables (Activerecord) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Eav Pattern Db Tables (Activerecord)

#1 User is offline   pommeverte 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 15-March 11

Posted 16 March 2013 - 07:07 AM

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
0

#2 User is offline   pommeverte 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 15-March 11

Posted 17 March 2013 - 05:28 AM

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.
0

#3 User is offline   Haensel 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 444
  • Joined: 14-January 11
  • Location:Vienna (Austria)

Posted 18 March 2013 - 05:32 AM

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

#4 User is offline   pommeverte 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 15-March 11

Posted 18 March 2013 - 05:40 PM

View PostHaensel, on 18 March 2013 - 05:32 AM, said:

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.


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)
0

#5 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 2,601
  • Joined: 10-October 10
  • Location:Denmark

Posted 18 March 2013 - 05:44 PM

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 )
"Less noise - more signal"
0

#6 User is offline   Cherif 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 27
  • Joined: 05-June 09
  • Location:Algeria

Posted 01 April 2013 - 03:07 PM

EAV is a database anti-pattern Link
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users