Order On Joining Table With Relation

Yii 1.1 General Discussion
1

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!

2

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'),
3

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'
4

Finally I found the solution.


return array(

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

'with'=>'contact',

'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:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Hope this solution can be helpfull for others.

@MewsicLovr, thanks for your fast response!

Kind regards