Yii Framework Forum: Just learned: CGridView and HAS_MANY Relation with Through - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Just learned: CGridView and HAS_MANY Relation with Through Rate Topic: ***** 4 Votes

#1 User is offline   codeJunkie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 06-October 11

Posted 16 February 2012 - 02:35 PM

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

#2 User is offline   haimwd 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 11
  • Joined: 27-February 12
  • Location:Israel, Afula

Posted 19 April 2012 - 02:25 AM

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
0

#3 User is offline   codeJunkie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 06-October 11

Posted 19 April 2012 - 12:11 PM

View Posthaimwd, 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).
0

#4 User is offline   chefandy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 13-May 12

Posted 13 May 2012 - 03:46 PM

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

#5 User is offline   rackycz 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 49
  • Joined: 16-December 09

Posted 16 July 2013 - 05:08 AM

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

#6 User is offline   codeJunkie 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 06-October 11

Posted 16 July 2013 - 08:48 AM

View Postrackycz, 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.
0

#7 User is offline   rackycz 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 49
  • Joined: 16-December 09

Posted 17 July 2013 - 01:01 AM

View PostcodeJunkie, 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..
0

#8 User is offline   supernugy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 26-July 13

Posted 27 July 2013 - 01:03 AM

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

#9 User is offline   Igor Savinkin 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 18
  • Joined: 30-July 13

Posted 13 August 2013 - 06:44 AM

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

#10 User is offline   Andrea.p 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 13-September 13

Posted 21 September 2013 - 05:39 AM

GREAT!!! Thanks for share!!!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users