Another MANY to MANY scenario

Yii 1.1 General Discussion
1

I have seen many posts and articles about how to deal with MANY::MANY relationships in Yii and they all seem to be based on differing scenarios - none of which are quite like mine …

My requirement is one that I would have thought was quite common, I have a system_options table which holds look-up values for various tables. Some tables can have multiple values and therefore have an intermediate table as follows:-




+-----------+         +------------------+         +-------------+    

| property  |         | property_options |         | options     |

|-----------|-------->|------------------|-------->|-------------| 

| id        |         | id               |         | id          |

| ...       |         | property_id      |         | option_name |

|           |         | option_id        |         | option_value|

+-----------+         +------------------+         +-------------+

with sample data where a property can have a Pool, Internet, and Jacuzzi AND can be a Villa but not an Apartment




  124                   nnn | 124 | 26                26 | feature | Pool

                        nnn | 124 | 44                44 | feature | Internet

                        nnn | 124 | 99                99 | feature | Jacuzzi

                        nnn | 124 | 110              110 | type    | Villa

                                                     111 | type    | Apartment

I have a model for each table but define the relationship in the Property model as follows:


          'options'=>array(self::MANY_MANY, 'Options', 'property_options(property_id, option_id)',),

What I am having difficulty with, is how to select a list of features for a property from options. In other words, a condition on the relation…


          'options'=>array(self::MANY_MANY, 'Options', 'property_options(property_id, option_id)',

                 'condition'=>'option_name=\'type\''),

However this adds the condition to the WHERE clause and not part of the OUTER join so doesn’t work.


SELECT `t`.`id` AS `t0_c0`, `t`.`property_id` AS `t0_c1`,

`t`.`option_id` AS `t0_c2`, `option`.`id` AS `t1_c0`,

`option`.`option_name` AS `t1_c1`, `option`.`option_code` AS `t1_c2`,

`option`.`option_value` AS `t1_c3`, `option`.`sort_order` AS `t1_c4` FROM

`property_options` `t`  LEFT OUTER JOIN `options` `option` ON

(`t`.`option_id`=`option`.`id`)  WHERE ((option_name='feature') AND

(property_id=28))

rather than


SELECT `t`.`id` AS `t0_c0`, `t`.`property_id` AS `t0_c1`,

`t`.`option_id` AS `t0_c2`, `option`.`id` AS `t1_c0`,

`option`.`option_name` AS `t1_c1`, `option`.`option_code` AS `t1_c2`,

`option`.`option_value` AS `t1_c3`, `option`.`sort_order` AS `t1_c4` FROM

`property_options` `t`  LEFT OUTER JOIN `options` `option` ON

(`t`.`option_id`=`option`.`id` AND (option_name='feature'))  WHERE 

(property_id=28)

Should I therefore create a scope within the Options model for each "feature" or "type"




       public function scopes() 

        {

           return array(

                'PropertyTypes'=>array(

                    'with'=>array('option'),

                    'condition'=>'option_name=\'type\''),

                'PropertyFeatures'=>array(

                    'with'=>array('option'),

                    'condition'=>'option_name=\'feature\''),

            );

        }

But I can’t figure out how to reference this from the property model …

Many thanks for any hints.

2

Maybe you can set up a ‘through’ type of relation with conditions?

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through

3

I don’t think you need to do anything but specify a “join” clause instead of a “condition” clause

instead of:




'options'=>array(self::MANY_MANY, 'Options', 'property_options(property_id, option_id)',

                 'condition'=>'option_name=\'type\''),







'options'=>array(self::MANY_MANY, 'Options', 'property_options(property_id, option_id)',

                 'join'=>'option_name=\'type\''),

It’s documented at: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-options

You might need to massage this.

4

Thanks, I’ve tried “through” but it seems that it iss just another way of doing the property_options(property_id, option_id)’) relation and not filtering the results at the other end of the relationship.

5

Thanks for the suggestion. This option seems to enable you to add another JOINed table and gets inserted at the end of the SELECT statement. it doesn’t affect the already defined JOIN.

relations




       'propertyFeatures'=>array(self::MANY_MANY, 'Options', 'property_options(property_id, option_id)',

                            'join'=>'INNER JOIN `property_options`

                                `propertyFeatures0` ON

                                (option_name=\'feature\')'),

generated SQL




SELECT `propertyFeatures`.`id` AS `t1_c0`,

`propertyFeatures`.`option_name` AS `t1_c1`,

`propertyFeatures`.`option_code` AS `t1_c2`,

`propertyFeatures`.`option_value` AS `t1_c3`,

`propertyFeatures`.`sort_order` AS `t1_c4` FROM `options`

`propertyFeatures` 

//  This bit added

INNER JOIN `property_options`

`propertyFeatures0` ON (option_name='feature') 

// to here

INNER JOIN `property_options`

`propertyFeatures_propertyFeatures` ON

(`propertyFeatures_propertyFeatures`.`property_id`=:ypl0) AND

(`propertyFeatures`.`id`=`propertyFeatures_propertyFeatures`.`option_id`)
6

Nevermind the answer I thought I had for you doesn’t apply to many:many, I’m not sure how to do this but it shouldn’t be this hard.

7

It never is when you know how … :D

8

I think the first answer was the correct one, you will need to use through with the addition of an ‘on’ clause to one of the relations. So:




'propertyOptions'=>array(self::HAS_MANY, 'PropertyOptions', 'on' => 'option_name=\'type\'',

'options'=>array(self::HAS_MANY, 'Options', 'through' => 'propertyOptions',
9

Excellent, it works!

It was a combination of the two:


'propertyFeatures'=>array(self::MANY_MANY, 'Options', 'property_options(property_id, option_id)', 'on' => 'option_name=\'feature\'',

So, thank you both very much!