With() - Table Alias Name Clash

Yii 1.1 General Discussion
1

Hi. I’m just trying out ‘with()’ statements to make more efficient database queries.

If I use the with method with the following it causes JOINs between band<->label, band<->group, group<->members, and label<->group:


$bands=Band::model()->with('label', 'group', 'group.members')->findAll();

If I want to introduce another JOIN between label<->group I get an error saying “not unique table/alias: ‘group’”. Looking at the SQL generated, it’s tried to create two table aliases called ‘group’, one for the label<->group relation, and one for the band<->group relation.


$bands=Band::model()->with('label', 'group', 'group.members', 'label.group')->findAll();

Is there a way I can manually change the alias for the other table so they don’t clash? I have tried changing the relation name from ‘group’ to ‘labelGroup’ in the Label class definition which works, but for other reasons I would like the keep the relation names the same.

I would have thought Yii should detect this and create unique aliases in both cases.

Any thoughts?

2

Hi grubstarstar, welcome to the forum.

You can use ‘alias’ option of the relation.




$bands=Band::model()->with(

    'label',

    'group',

    'group.members',

    'label.group' => array('alias' => 'group2'),

)->findAll();

Options can be set on-the-fly.

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#relations-detail

3

Ok, just found the answer. You can specify an alias when defining the relation:


'group' => array(self::BELONGS_TO, 'Group', 'group_id', 'alias'=>'labelGroup'),

so,


$bands=Band::model()->with('label', 'group', 'group.members', 'label.group')->findAll();

now works as this alias is used in the background.

4

oh great, you can do it in the ‘with’ method call as well. I posted my answer before I saw this. Thanks for the quick response! There’s quite a lot of options that aren’t always apparent at first.