With() - Table Alias Name Clash

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?

Hi grubstarstar, welcome to the forum.

You can use ‘alias’ option of the relation.





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


Options can be set on-the-fly.


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

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


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

now works as this alias is used in the background.

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.