Yii: CGridView - two rows from parent-table

Yii: 1.1.15

Till now I made a lot with CGridView - now I get stuck with a simple thing: In CGridView I want to show data from two different rows from a parent-table:

table/model: people (parent-table)

id    name
1     Bush
2     Heineken
3     Miller
4     Gonzales

table/model: friends (child-table)

id    friend1_id friend2_id
1     1          3
2     2          4

In the model Frinds.php I have a relation:

public function relations() {
    return array(
        'people' => array(self::BELONGS_TO, 'People', 'friend1_id')
}

In my CGridview I want to see - and I don't know how:

id  friend1   friend2
1   Bush      Miller
2   Heineken  Gonzales

Solution

So I understand you've got a People table where you store all users and a Friends table where you store a row for every friendship between two users.

Now I guess your grid is getting results for Friends model right?

You should:

1) Define properly two relations inside Friends model:

public function relations() {
    return array(
        'friend1' => array(self::BELONGS_TO, 'People', 'friend1_id'),
        'friend2' => array(self::BELONGS_TO, 'People', 'friend2_id'),
    );
}

2) Now in your Friends model add two new properties:

class Friends extends CActiveRecord {

  public $friend1Name;
  public $friend2Name;

...

3) Now in your grid you can use this kind of columns:

'columns' => array(
    'friend1Name'=>array(
        'name'=>'friend1Name',
        'value'=>'$data->friend1->name',
    ),
    'friend2Name'=>array(
        'name'=>'friend2Name',
        'value'=>'$data->friend2->name',
    ),

4) The two properties you've added have now no label, you can customize it from attributeLabels:

public function attributeLabels() {
    return array(
      'friend1Name' => 'Friend 1 name',
      'friend2Name' => 'Friend 2 name',

5) Additionally you can achieve two more things if you tweak your Friends model search() method:

Since you're lazy-loading models, Yii will make a sql request to database to get every friend, if you show 10 results per page, that will be 20 requests, to turn that into only one you can use the $criteria->with(), and put there the friend1 and friend2 relations with joinType inner join.
Second, you can achieve sorting the results when you click your grid columns, for that you can add sort configuration in the CActiveDataProvider returned by the search() method and map the friend1Name and friend2Name to sort by their corresponding fields in the relations.

Update: Additional points:

public function search() {

    //... compare conditions here

    /* 6) Run only one SQL query by joining tables instead of one query per every Person */
    $criteria->with=array(
        'friend1'=>array(
            'joinType'=>'INNER JOIN',
        ),
        'friend2'=>array(
            'joinType'=>'INNER JOIN',
        ),
    );

    /* Tip: You can use with() and not use the joinType but by using it we are forcing a INNER JOIN that makes sense in this case */

    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,

        /* 7) Apply sorting for the columns from the related tables */
        'sort'=>array(
          'attributes'=>array(
              '*',
              'friend1Name'=>array(
                  'asc'=>'friend1.name',
                  'desc'=>'friend1.name desc',
              ),
              'friend2Name'=>array(
                  'asc'=>'friend2.name',
                  'desc'=>'friend2.name desc',
              ),
          ),
        ),
    ));

}

/* 8) Auto-fill friend1Name and friend2Name with the names from the related tables */
protected function afterFind () {

    $this->friend1Name=$this->friend1->name;
    $this->friend2Name=$this->friend2->name;

    // Tip: Now you can use in your grid directly friend1Name instead of $data->friend1->name

    parent::afterFind();
}