Yii multiple relations to same table

Question

I have the following relations defined in my UserBan model:

public function relations()
 {
     // NOTE: you may need to adjust the relation name and the related
     // class name for the relations automatically generated below.
     return array(
         'user' => array(self::BELONGS_TO, 'User', 'userId'),
         'target' => array(self::BELONGS_TO, 'User', 'targetId'),
         'author' => array(self::BELONGS_TO, 'User', 'authorId'),
     );
 }

Now when I try to do:

$criteria->with = array('user', 'target');

It screams the following because the User mdel has a default scoped relation to Nicknames:

Not unique table/alias: 'nicknames'

SQL:

    SELECT COUNT(DISTINCT `t`.`id`) FROM `userban` `t` 
LEFT OUTER JOIN `user` `user` ON (`t`.`userId`=`user`.`id`) 
LEFT OUTER JOIN `nickname` `nicknames` ON (`nicknames`.`userId`=`user`.`id`) 
LEFT OUTER JOIN `user` `target` ON (`t`.`targetId`=`target`.`id`) 
LEFT OUTER JOIN `nickname` `nicknames` ON (`nicknames`.`userId`=`target`.`id`) 
WHERE ((user.name LIKE :username) AND (:dateStart<=t.createdAt AND :dateEnd>=t.createdAt))

How do I get over this? Where do I "alias" my joined tables ?

EDIT Here is the default scope for the User model:

 public function defaultScope()
     {
         return array(
             'with' => 'nicknames',
             'together' => true
         );
     }

as a hint, also, create indexes for each key needed – Ionut Flavius Pogacian May 20 '13 at 12:33 — Ionut Flavius Pogacian, May 20 '13 at 12:33

Jon · Accepted Answer · 2013-05-20T14:45:34.290

8

When you define multiple relations to the same table it's a good idea to specify unique aliases for each one. You do that when specifying the relations:

return array(
     'user' => array(self::BELONGS_TO, 'User', 'userId', 'alias' => 'unick'),
     'target' => array(self::BELONGS_TO, 'User', 'targetId', 'alias' => 'tnick'),
     'author' => array(self::BELONGS_TO, 'User', 'authorId', 'alias' => 'anick'),
 );

See the documentation for CActiveRecord::relations for more information.

Update: It seems you also need to use different aliases for joining the nicknames table in your default scope. I 'm not sure what the best way to do that would be, but this works and it's easy to do:

public function defaultScope()
{
    static $counter = 0;

    return array(
        'with' => array(
            'nicknames' => array('alias' => 'nick'.($counter++))
        ),
        'together' => true,
    );
}

edited May 20 '13 at 14:45

answered May 20 '13 at 10:27

Jon

428,835
81
738
806

Cool, didn't know you could define aliases in the relations array. – Joe Miller May 20 '13 at 10:52
I don t think I get what would this solve. The query still has the same issue: https://gist.github.com/samsonradu/5611950 – Samson May 20 '13 at 12:33
It s not the multiple relation itself that causes this problem. Its the multiple relation's default scope, which is the same for every user model no matter how I call it. - correct me if I m wrong here :) – Samson May 20 '13 at 12:56
@Samson: It's not the *same* issue; the query still doesn't run, but you can see there has been improvement. For the default scope I 'm not sure right now how to solve the problem -- does [this](https://gist.github.com/defacer/8fad3f3a6b01a57f43bd) do it perhaps? – Jon May 20 '13 at 13:58
I know it s not the same issue, sorry, but it s not addressing my issue. I tried your gist code but instead of aliasing the relation it aliases the User model itself – Samson May 20 '13 at 14:17
@Samson: Duh... stupid me. Please check the new revision of the gist. – Jon May 20 '13 at 14:32

Yii multiple relations to same table

1 Answers1