[SOLVED] How to create custom relation for active record

Dear everyone,

I have two tables with relation that doesn’t use normal primary key and foreign key.

For example, I have…

Table : User

  • id (pk)

  • name

  • username

Table : Profile

  • id (pk)

  • username

for some reason I need to join User and Profile (User has one Profile) using column username instead of using primary key.

I created AR model for table User like this, which doesn’t works!

class User extends CActiveRecord


  // Just leave out all others default functions such as model, rules.. 

  public function relations()


     return array(

       'profile' => array(self:HAS_ONE, 'Profile', 'username'),




And for AR model for table Profile there’s nothing special.

The reason is Yii tried to use User’s id to join with Profile’s username which is default behavior of the AR.

Like this…

SELECT * FROM User t LEFT JOIN Profile.username = t.id

Well, obviously it doesn’t work.

So I would like to customize this to make AR model is able to join by using User’s username and Profile’s username. To make it is able to do something like this.

SELECT * FROM User t LEFT JOIN Profile.username = t.username

It’s doable in SQL. But for Yii, How?

If you say, why don’t change the table? Well, it’s not my system. I can’t change it but I have to retrieve data from it.

Anyone have any suggestion? (I believe Yii can do it but I couldn’t find it)

Thanks in advance.

I read the document but I couldn’t find any solution (may be my fault that I couldn’t find it).

See this post

In your case

profile = array(self::HAS_ONE, Profile, '', 'on'=>'profile.username = t.username');


Wow! Thanks.

Never know that without specify ‘ForeignKey’ in relation parameter array will do the trick.