[codeJunkie]

It took me a good bit to figure out how to display a relation that goes through a table. I'll try to document everything I did to get this to work with examples. The tutorials were helpful, but I felt there was just a tad bit of information missing from them. Such as an example of using the returned relational data in a widget that required a CAvtiveDataProvider and in what form this information was returned as.

Database:
kid

• ID (PK)
  
• name
  
• age

toyBox

• ID (PK)
  
• kidID (FK -> kid.id)

toy

• ID (PK)
  
• toyBoxID (FK -> toyBox.id)
  
• name

My goal is to display a CGridView of all the 'toys' that a 'kid' had.
Since a 'kid' could have many 'toyBox' items but a 'toyBox' can only belong to one 'kid' and 'toyBox' can have many 'toy' items, but 'toy' can only belong to one 'toyBox'.

So I first added the relation to the Kid model:

//For the Kid model
public function relation(){
    return array(
        ...
        'toyBoxes' => array(self::HAS_MANY, 'Kid', array('ID'=>'kidID'),
        'toys' => array(self::HAS_MANY, 'Toy', array('ID'=>'toyBoxID'), 'through'=>'toyBoxes'),
        ...
    );
}

For the KidController to display the view:

public function actionView($id) {
        $kidModel = Kid::model()->findByPk($id)
        // use "$kidModel = $this->loadModel($id);" if you auto generated and this function is available 

        //render content
        $this->render('view', array(
            'kidModel' => $kidModel,                      
        ));
    }

The $kidModel->toys returns an array of Toy Objects with the key of the Toy Objects Primary Key.
Then I had to render the view of the Kid model with the CGridView of all the toys that Kid owned:

<?php
    $this->widget('zii.widgets.grid.CGridView', array(
        //It is important to note, that if the Table/Model Primary Key is not "id" you have to
        //define the CArrayDataProvider's "keyField" with the Primary Key label of that Table/Model.
        'dataProvider' => new CArrayDataProvider($kidModel->toys, array('keyField'=>'ID')),
        'columns' => array(
            'ID',
            'name',
        ),
    ));
    ?>

I hope I did not repeat anything and this was helpful.

[haimwd]

Very good thing to do but i found a little better solution:

'dataProvider' => new CActiveDataProvider('Toy', array('data'=>$kidModel->toys)),

this allow you to use the CActiveRecord attribute names

[codeJunkie]

haimwd, on 19 April 2012 - 02:25 AM, said:

Very good thing to do but i found a little better solution:

'dataProvider' => new CActiveDataProvider('Toy', array('data'=>$kidModel->toys)),

this allow you to use the CActiveRecord attribute names

I tried this with my current project, but the application kept giving me errors/render problems. It might have been the way the database was set up (SQL Server with federal security protocols that make no sense).

[chefandy]

Word, this is exactly what I was looking for. Thanks!

[rackycz]

Hi. I have the same problem as was in the first post, but I can't use primary keys. I have to create relation from table KID to TOY using THROUGH relation but without any primary key. I have to specify related columns manually, but I totally do not understand this.
Relations are hell in Yii for me.

My situation is like this:
Table KID contains column "kid_nick" (not PK of course). Table TOYBOX contains "nick_kid" (different column name). Here I need to establish 1st relation.
Second relation will be from table TOYBOX to TOY. TOYBOX contains column "toy_name" and TOY also has column "toy_name".
So I will find a kid by it's nick. Based on this nick I will find particular toyboxes. Records with these toyboxes contain "toy_name" and this has to be found in table "toy".
Using this algorithm I have to show all toys for particular kid using CGridView, sort it and page it.

Please, don't ask me why it is without PKs, it has to be like this :-o

PS: Or I could use Many_Many relation, but I can't define relation because column names are not the same in all 3 tables.

[codeJunkie]

rackycz, on 16 July 2013 - 05:08 AM, said:

Hi. I have the same problem as was in the first post, but I can't use primary keys. I have to create relation from table KID to TOY using THROUGH relation but without any primary key. I have to specify related columns manually, but I totally do not understand this.
Relations are hell in Yii for me.

My situation is like this:
Table KID contains column "kid_nick" (not PK of course). Table TOYBOX contains "nick_kid" (different column name). Here I need to establish 1st relation.
Second relation will be from table TOYBOX to TOY. TOYBOX contains column "toy_name" and TOY also has column "toy_name".
So I will find a kid by it's nick. Based on this nick I will find particular toyboxes. Records with these toyboxes contain "toy_name" and this has to be found in table "toy".
Using this algorithm I have to show all toys for particular kid using CGridView, sort it and page it.

Please, don't ask me why it is without PKs, it has to be like this :-o

PS: Or I could use Many_Many relation, but I can't define relation because column names are not the same in all 3 tables.

Not having a PK worries me a fair bit in terms of database design and I would have a discussion on redesigning the database to be at least 3rd normal form.
What I would use is a column that would uniquely identify a row/kid/toy and use this column as the PK.

I'm afraid that is the best I can provide at this moment since I no longer use Yii (sad I know, but my job uses the symfony2 framework now) and I am a bit out of practice with it.

[rackycz]

codeJunkie, on 16 July 2013 - 08:48 AM, said:

Not having a PK worries me a fair bit in terms of database design and I would have a discussion on redesigning the database to be at least 3rd normal form.
What I would use is a column that would uniquely identify a row/kid/toy and use this column as the PK.

I'm afraid that is the best I can provide at this moment since I no longer use Yii (sad I know, but my job uses the symfony2 framework now) and I am a bit out of practice with it.

Thanks for reply. Unfortunately, our tables are more complex and we have more keys in them. Usually they are composite.
Never mind, I will try to use CGridView with pure SQL, it is fastest anyway..

[supernugy]

Exactly what I need right now, will try it out right away.

[Igor Savinkin]

[size=2]i've tried to do the same and it worked but only for the parent model attributes (Kid). How could i insert the Toy model attributes in CArrayDataProvider for display? In your code

...[/size]
 'dataProvider' => new CArrayDataProvider($kidModel->toys, array('keyField'=>'ID')),
        'columns' => array(
            'ID',
            'name',

The 'name' column down in here referes to which attribute, of Toy model or of Kid model?

Also if there is similar example for EditableGridView?

[Andrea.p]

GREAT!!! Thanks for share!!!