AR questions

Yii 1.1 General Discussion
1

I have these classes:


class User extends CActiveRecord

{

...

    public function relations()

    {

        return array(

            'user_details' => array(self::HAS_MANY, 'UserDetails', 'user_id'),

            'users_applications' => array(self::HAS_MANY, 'UsersApplications', 'user_id'),

        );

    }

}


...


class UserDetails extends CActiveRecord

{

...

    public function relations()

    {

        return array(

            'user' => array(self::BELONGS_TO, 'User', 'user_id'),

            'division' => array(self::BELONGS_TO, 'Division', 'division_id'),

            'department' => array(self::BELONGS_TO, 'Department', 'department_id'),

        );

    }

}


...


class Division extends CActiveRecord

{

...

    public function relations()

    {

        return array(

            'departments' => array(self::HAS_MANY, 'Department', 'division_id'),

            'user_details' => array(self::HAS_MANY, 'UserDetails', 'division_id'),

        );

    }

}

I create a CDbCriteria object, which I will use to find all User objects with their relations. How do I write the select property if I want to select specific columns from the UserDetails and Division objects?




$criteria = new CDbCriteria();

$criteria->with = array('user_details', 'user_details.division', 'user_details.department');

$criteria->select = array(

    't.id',

    't.lastname',

    'user_details.telephone',

    'division.name',

);

This sample code throws an exception, saying that it cannot find user_details.telephone. One change regarding AR in 1.1.0 is that relational tables are now prefixed with the name of the relation. So, why doesn’t this code work? What should I write in order to select the relation of the relation of User (for example the department or division of a user)?

I’m sure I’m missing something here…

2

Did you check what the generated SQL is?

3

No, there isn’t any generated SQL, since the exception is thrown. If I omit the select property and the query runs with all columns selected, the generated SQL contains the columns just like I write them in the select property, so it seems rather weird it doesn’t work in the first place. For example:




SELECT `t`.`id` AS `t0_c0`, `t`.`firstname` AS `t0_c1`, `t`.`lastname` AS `t0_c2`, `user_details`.`id` AS `t1_c0`, `user_details`.`user_id` AS `t1_c1`, `user_details`.`division_id` AS `t1_c2`, `user_details`.`department_id` AS `t1_c3`, `user_details`.`telephone` AS `t1_c4`, `user_details`.`computer_name` AS `t1_c5`, `user_details`.`is_regular_empl` AS `t1_c6`, `user_details`.`position` AS `t1_c7`, `user_details`.`has_internet` AS `t1_c8`, `user_details`.`comments` AS `t1_c9`, `user_details`.`date_created` AS `t1_c10`, `user_details`.`date_modified` AS `t1_c11`, `user_details`.`date_disabled` AS `t1_c12`, `division`.`id` AS `t2_c0`, `division`.`name` AS `t2_c1`, `division`.`enabled` AS `t2_c2`, `department`.`id` AS `t3_c0`, `department`.`name` AS `t3_c1`, `department`.`division_id` AS `t3_c2`, `department`.`enabled` AS `t3_c3` FROM `users` `t`  LEFT OUTER JOIN `user_details` `user_details` ON (`user_details`.`user_id`=`t`.`id`) LEFT OUTER JOIN `divisions` `division` ON (`user_details`.`division_id`=`division`.`id`) LEFT OUTER JOIN `departments` `department` ON (`user_details`.`department_id`=`department`.`id`) WHERE (1=1 AND `user_details`.`division_id` = 1)
4

Just passed through my mind.

The relation between User and UserDetails is one to many. Is it because for one User multiple UserDetails could be returned that I cannot explicitly declare the columns in the select property of CDbCriteria?

5

The one to many is indeed the reason it can’t execute this query. Try to GROUP BY user.

6

Yes, on top of what passed through my mind, the solution is to use CDbCriteria objects for every with parameter. For example:




$criteria->with = array(

    'user_details' => array('select' => 'id, telephone, computer_name'),

    'user_details.division',

    'user_details.department');

instead of putting telephone, computer_name in the CDbCriteria object used for the User model.

That’s why it didn’t work in the first place.