CGridView get relationship value

Yii 1.1 General Discussion
1

Hello guys,

I’ve been reading a lot of topics about this, but none of them have answered my question.

I have two tables (well, 3 but 2 for the example).

-Countries

–countryId

–countryName

-Teams

–teamId

–teamCountry

teamCountry is related to countryId.

These are my relationships:




     

    //Team relations

                    return array(

                            'players' => array(self::HAS_MANY, 'Players', 'playerTeam'),

                            'countries' => array(self::HAS_ONE, 'Countries', 'countryId'),

                    );

     

     

    //Countries relations

                    return array(

                            'players' => array(self::HAS_MANY, 'Players', 'playerCountry'),

                            'teams' => array(self::HAS_MANY, 'Teams', 'teamCountry'),

                    );

And this is a part of my admin model:


    //Teams admin

    $this->widget('zii.widgets.grid.CGridView', array(

            'id'=>'teams-grid',

            'dataProvider'=>$model->search(),

            'filter'=>$model,

            'columns'=>array(

                    'teamId',

                    'teamName',

                    array('name'=>'teamCountry', 'value'=>'$data->countries->countryName'),

                    'teamRanking',

                    array(

                            'class'=>'CButtonColumn',

                    ),

            ),

    ));

As you could see, I’m trying to list the country name assigned to each team, instead of its ID.

Instead of showing me the correct name (I have Portugal -id:1- assigned to every team) and it is showing me all the countries that exist.

For example, I have 3 teams: Team 1, Team 2, Team 3; all assigned to Portugal (id:1).

It will show me every row in the Countries table:

Team 1 - Portugal

Team 2 - Brasil

Team 3 - England

etc

Any suggestions?

2

Hi there,

At least one of your relations is incorrect:


//Team relations

                    return array(

                            'players' => array(self::HAS_MANY, 'Players', 'playerTeam'),

                            //WRONG 'countries' => array(self::HAS_ONE, 'Countries', 'countryId'),

                            //CORRECT

                            'countries' => array(self::BELONGS_TO, 'Countries', 'teamCountry'),

                    );

A normal Gii CRUD should have generated the correct relations ;)

And then you can use in your CGridView


'columns'=>array(

    …

    'countries.countryName'

Anyway, read here to see the differences between relations types

Yii Guide: Relational Active Record

Relations Wiki: BELONGS_TO versus HAS_ONE

3

Hello!

Thank you for answering.

I’ve already tried something similar to that before, and with that code I get a CException:


CException


Property "Teams.countryId" is not defined.

And one more thing… I really don’t understand why it should be BELONGS_TO. I’ve read the links you gave me, but it seems to me that Team should have one country… Why it should belong to country if it isn’t dependable on it?

Any suggestions?

Thanks!

4

Maybe the best would be to write it out in a normal sentence:

  • A country has many teams

  • A team belongs to a country

So its:

COUNTRY ===> TEAM

The BELONGS_TO relation is always the other side of a HAS_MANY relationship.

5

But why is the team belonging to country and not the inverse?

And by the way, I solved it don’t know really how.

I changed this in the relation:




//old

		return array(

			'players' => array(self::HAS_MANY, 'Players', 'playerTeam'),

			'countries' => array(self::BELONGS_TO, 'Countries', 'countryId'),

		);


//new

		return array(

			'players' => array(self::HAS_MANY, 'Players', 'playerTeam'),

			'countries' => array(self::BELONGS_TO, 'Countries', 'teamCountry'),

		);

And it started showing up the team country name correctly. Why does this work?

6

Well the inverse… is not possible. I don’t know how to say it :) Especially that in your scheme, you say “teamCountry is related to countryId” so you seem familiar with relational databases?

Let me ask you: If countries could belong to teams, why didn’t you put teamId in the table Countries?

Well that’s what I told you to change in my first post ;)

7

Oh, I think I got the point, thanks!

Yeah, now that you say that, I’m seeing that I’ve just tried the BELONGS_TO but forgot the last part :) So let me look at it again:


'countries' => array(self::BELONGS_TO, 'Countries', 'teamCountry'),

‘countries’ is the relationship name

BELONGS_TO is the relationship type

Countries is the model it is being connected to

teamCountry is the relational value of the current model that will connect to the primary key of the Countries model?

8

Exact! teamCountry is called a foreign key.

9

Thanks!

So, as Yii’s documentation says, to create automatically this, the comment on table field should be the following?


CONSTRAINT FOREIGN KEY (teamCountry) REFERENCES Countries(teamCountry)
10

Not exactly, it should be:

[sql]… COMMENT ‘CONSTRAINT FOREIGN KEY (teamCountry) REFERENCES Countries(countryId)’[/sql]

Or use an innodb database and implement constraints, two birds with one stone :)

11

Argh… that’s what I had, and I thought changing it to what I said in my last reply.

The problem would be just with the change to BELONGS_TO and the FK, right?

12

I guess.