How To Sort Many-To-Many Relation On Column In Join Table

Yii 1.1 General Discussion
1

I have two tables, ‘home’ and ‘tabs’ with a many-to-many relation. I also have a join table ‘home_tabs’ which is just the keys of each in addition to a displayorder column.

In the Home model I have the following relation




'tabs' => array(self::MANY_MANY, 'Tabs', 'home_tabs(home_id, tabs_id)'),

I want to sort the result of tabs based on the displayorder column in home_tabs. This works if I use the order clause in the relation like this




'tabs' => array(self::MANY_MANY, 'Tabs', 'home_tabs(home_id, tabs_id), 'order'=>'displayorder ASC'),

However, my problem is that field name is ambiguous (other tables use that same column name) and when I combine this with a larger query it throws an error. Is there a way to manually set the alias for the join table so I can use that in my relation? I looked at what Yii is doing when it creates the query and it is giving it the name ‘tabs_tabs’ so if I hardcode that in it works but I’m not positive it will always be that so I’d rather explicitly set the name or call it on the fly when the code executes.

I’ve tried setting the alias in the relation but that seems to apply it to the Tabs model, not the actual join table model. Anyone have any ideas or is this some obvious thing I’m just missing? Thank you.

2

Hi ScottB, welcome to the forum.

I would recommend you to split your MANY_MANY relation to a combination of 2 BELONGS_TO/HAS_MANY relations.

3

Hey softark,

Thank you for the reply. I think I see what you mean. Here is how I’ve updated everything, let me know if this makes sense (it appears to be working but I have a very limited data set for testing right now)

My home model now has this relation:




'home_tabs' => array(self::HAS_MANY, 'HomeTabs', 'home_id'),

And HomeTabs has the following relations:




'home' => array(self::BELONGS_TO, 'Home', 'home_id'),

'tabs' => array(self::BELONGS_TO, 'Tabs', 'tabs_id'),

And additionally I have updated the default scope of HomeTabs like this (to take care of ordering)




public function defaultScope() {


    $t = $this->getTableAlias(false, false);

    return array(

        'order'=>$t.'.displayorder ASC',

    );

}

And instead of referencing the tabs through $model->tabs I instead do:




$model = Home::model()->with(array('home_tabs'=>array('with'=>array('tabs'))))->find(); // there is only ever one row

$tabs = $model->home_tabs->tabs;

Seems to be working with no ambiguity errors so far.

4

Yes, it looks fine to me too. :)