Order On Joining Table With Relation

Hi there,

I’m sorry if my question is easy, but I struggled with it for some time now and couldn’t find anything about it.

I have two tables:

  • Adressbook (user_id, contact_id)

  • User (id, name, email, …)

So there are two relations between the models, the:

  • userAddressBookContains, which returns the contact_id’s for the entries where the user_id equal to the user’s

  • isInAddressBook, which return the user_id’s for the entries where the contact_id is equal to the user’s id

When I try to display the addressbook of a certain user, I would like to sort it by name.

I use the following code to retrieve the addressbook:

$user = User::model()->findByPk(Yii::app()->user->id); 

$addresses = $user->userAddressBookContains;

The relations in: User

return array(

'userAddressBookContains' => array(self::HAS_MANY, 'Addressbook', 'user_id'),

'isInAddressBook' => array(self::HAS_MANY, 'Addressbook', 'contact_id'),

The relations in: AddressBook are:

return array(

'user' => array(self::BELONGS_TO, 'User', 'user_id', 'order'=>'user.name', 'alias'=>'user'),

'contact' => array(self::BELONGS_TO, 'User', 'contact_id'),


As you see I’ve tried the ‘order’-clause in the relations function, but didn’t know what I have to enter after userAddressBookContains. How can I order on user.name?

I hope you can help me with my issue!

Thank you in advance!

You have the order clause in the wrong class, it needs to be in the User class relations. (you might need to change the order alias):

return array(

'userAddressBookContains' => array(self::HAS_MANY, 'Addressbook', 'user_id', 'order'=>'user.name'),

'isInAddressBook' => array(self::HAS_MANY, 'Addressbook', 'contact_id'),

I had allready deleted the other one, but I don’t understand how I can reach the user table. When I use the code you send me I receive:

Column not found: 1054 Unknown column 'user.name' in 'order clause'. 

The SQL statement executed was: 

SELECT `userAddressBookContains`.`user_id` AS `t1_c0`, `userAddressBookContains`.`contact_id` AS `t1_c1` FROM `AddressBook` 

`userAddressBookContains` WHERE (`userAddressBookContains`.`user_id`=:ypl0) 

ORDER BY user.name. Bound with :ypl0='1'

Finally I found the solution.

return array(

'userAddressBookContains' => array(self::HAS_MANY, 'Addressbook', 'user_id', 


'order'=>'contact.name asc'),

So I needed to take the relation of Addressbook in the with statement before I could access the columns.

Found the solution by digging more into:


Hope this solution can be helpfull for others.

@MewsicLovr, thanks for your fast response!

Kind regards