Accessing An Indirect Table

I have the following table design:

Person

id (pk)

name

city_id (fk)

City

id (pk)

name

country_id (fk)

Country

id (pk)

name

Within Person model:


'city' => array(self::BELONGS_TO, 'City', 'city_id')

Within City model:


'country' => array(self::BELONGS_TO, 'Country', 'country_id')

'people' => array(self::HAS_MANY, 'Person', 'city_id')

Within Country model:


'cities' => array(self::HAS_MANY, 'City', 'country_id')

I want to display the country name from an instance of the Person model using CActiveDataProvider. I believe I need to this by passing a CDbCriteria object. The sql join that I’m trying to create is




select * from person

JOIN city ON person.city_id = city.id

JOIN country on country_id = country.id;



I tried doing this using


$criteria=new CDbCriteria;

$criteria->join  = 'JOIN city ON city_id = city.id';

$criteria->join .= ' JOIN country ON country_id = country.id';

But I get the error


Property "Person.country" is not defined.

I would prefer to not have to create any manual joins and just use relations. Is there any way to extend the relations so that I can access the country name? Is the table design incorrect perhaps?

this should solve your problem


Person::model()->with('city.country')->findAll();

To expand on alirz23’s answer, you’ll then be able to access the country name like so:




    foreach ($people as $person)

    {

        $countryName = $person->city->country->name;

    }



A little addition:

If you prefer, you can also define an "indirect" relationship in the Person model using "through".

See http://www.yiiframework.com/doc/guide/1.1/it/database.arr#relational-query-with-through

In that way, you could use $person->country->name (for example) in your code.

Regards,

Leandro

That did it, thanks for the quick answer!

Excellent, I was wondering if it was possible to define this. Gratitude!