CDbCriteria multiple with behavior

Yii 1.1 General Discussion
1

Hello everyone.

I’ve been merging criterias and noticed that “with” clauses on the same tables are being hooked up to each other with "AND"s and this is a bit of an issue for me. Let me try to expose what I mean with an example.

Say I have a user table/model in relation with a Property table/model. A user HAS MANY Property

Just to illustrate here would be my two tables:

USER

-id (int)

-username (varchar)

-category enum(‘model’,photographer’)

Property

-id (int)

-user_id (int)

-name (varchar)

-value (varchar)

Now say each user had two properties hair_color and eye_color and wanted to get the models with brown hair and then filter it down to those who also have green eyes.

(this is a fully fictional situation to illustrate my issue)

Here’s an example of the code:




$crit = new CDbCriteria(array( 'condition' => 't.category="model"'));


$hair_crit = new CDbCriteria(array('with'=> array( 'Property' => array('condition' => 'name = "hair_color" AND value = "brown"')),

                                   'together'  => true

                                       

                                  )

                             );


$crit->mergeWith($hair_crit);




$eye_crit = new CDbCriteria(array('with'=> array( 'Property' => array('condition' => 'name = "eye_color" AND value = "green"')),

                                   'together'  => true

                                  )

                             );


$crit->mergeWith($eye_crit);


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

			'criteria'=>$crit,

		));

Now my issue here is that it tries to find the Users with Properties such as ‘(name = “hair_color” AND value = “brown”) AND (name = “eye_color” AND value = “green”)’

Needless to say that this is impossible.

Can anyone point out where I’m going wrong? Thanks!

2

[font=arial, verdana, tahoma, sans-serif][size=2]The root of the problem is that CDbCriteria is designed to work with relational data and EAV pattern used here is not a relational concept. Is it possible to refactor your database structure in order to eliminate the need for EAV? (It would be fairly easy in the example, by creating "model" and "photographer" tables, but your real data structure may be much more complex.)[/size][/font]

3

Thanks for the reply. Unfortunately my data structure is indeed more complex so changing it at this point is not an option. That however cleared it up for me, I was wondering if CDbCriteria might’ve been flexible enough for this but if that isn’t the case I won’t sweat over it. I’ll just build the condition manualy or use some other workaround.

Thanks a lot!

4

Can you set the withs with as aliases so it creates two separate joins one for hair colour and one for eye colour?




$hair_crit = new CDbCriteria( array(

    'with'=> array(

       'Property' => array(

           'condition' => 'hair_color.name = "hair_color" AND hair_color.value = "brown"',

           'alias' => 'hair_color'

       )

    ),

    'together'  => true

) );

I’m not sure it works like that at all, just basing that of a vauge memory. Although it’s probably easier to create a database view and base an active record on that for this purpose, unless you’re changing the number of criteria each time. Stored Procedure perhaps?

5

Hey there, thanks for the reply! Setting up multiple aliases doesn’t seem to automatically create seperate joins.There might be something to do here… But I got lazy and ended up building the sql manualy and formatted it for CDbCriteria like I usualy do for more complex queries.

Thanks guys.