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?