[FIXED] Yii Define relation to same model through other model

Issue

In my Yii app I have 2 models, Profiles and Groups. Each profile belongs to a Group defined by Profiles.GroupID that is a FK to Groups.GroupID. Also, each Group has a manager (Group.ManagerID) which is basically another profile (therefore, it references a user_id in the table Users).

In model Profiles I would like to define a relation so that, for a given Profile I can easily retrieve the Manager.

Below is some sample data from the two tables that represent the 2 models Profiles, Groups:

Table Profiles
user_id  firstname    lastname     group_id
-------  -----------  ----------   -------  
1        John         Doe          100  
2        Walter       White        200  
3        Gus          Fring        100  
4        Biggie       Smalls       200  


Table: Groups
group_id   manager_id
-------    ---------
100        2
200        4

Any suggestions would be appreciated.

Solution

You can use relations, you must create two "BELONGS_TO" and "HAS_ONE" relations one in Profiles model and second in Groups model:

for example in Profiles:

public function relations() {
    'group' => ...
}

in Groups:

public function relations() {
    'manager' => ...
}

and than you can get manager of concrete user like this $user->group->manager.

But in this case generates query with two JOINs, which can be slow if your tables are big.

Or you can use only one relation (for getting user group) and one more query for getting manager: In Profiles model create public variable $manager and add it in your model rules (f.e in search)

public $manager = null;

and override method afterFind:

public function afterFind() {
    $this->manager = self::model()->findByAttributes('user_id' => $this->group->manager_id);
    return parent::afterFind()
}

EDITED

If you use the first method (two relations), you can override Sort object of your DataProvider (f.e. in method "sort" of your model):

public function search() {
        $criteria = new CDbCriteria;
        $sort = new CSort;
        $sort->defaultOrder = 'someField ASC';
        $sort->attributes = array(
            '*',
            'managerLastname' => array(
                'ASC' => 'group.manager.lastname ASC',
                'DESC' => 'group.manager.lastname DESC',
            )
        );
        // .....other criterias
        $criteria->compare('managerLastname', $this->group->manager->lastname, true); // for filtering

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => $sort
        ));
    }

and in CGgridView columns add/change column

array(
    'name'=>'managerLastname',
    'value'=>'$data->group->manager->lastName'
) // be sure that each user has manager

and add also public property managerLastname in your model.

Thanks.

Answered By - Taron Saribekyan