Just learned: CGridView and HAS_MANY Relation with Through

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.

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).

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

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.

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…

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

[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?

GREAT!!! Thanks for share!!!