Hello
I want to work with a "through" relation and in my opinion both the documentation in "The Definitive Guide to Yii" and the implementation is very weird, not exact and unusable for me.
After taking a look at the code in CActiveFinder.php this is how it works:
‘relationName’ => array(‘relationType’, ‘ClassName’, ‘foreign_key’, ‘through’ => ‘throughRelationName’)
Keep in mind that you can chain/nest relations with through.
For the first through relation, Yii takes
SELECT table.* FROM table
LEFT OUTER JOIN troughTable ON (troughTable.foreign_key = table.PRIMARY_KEY)
For every following relation Yii takes
SELECT table.* FROM table
LEFT OUTER JOIN troughTable ON (troughTable.PRIMARY_KEY = table.foreign_key)
See how the usage of the defined foreign_key and PRIMARY_KEY is changing. This is very weird. I think it should always work like in the second statement:
SELECT * FROM (table of ClassName) AS t1
LEFT OUTER JOIN (table of through relation ClassName) AS t2 ON (t2.PRIMARY_KEY = t1.foreign_key)
What Yii is doing at the moment is very unforeseeable. The funny thing is, it works very well for the example because the example is also weird. It pretends to be an example for HAS_MANY relations with through, but if you look at the ER, you see that the table role is a mappig table for a MANY_MANY relation between user and group.
Take a simple example with this 3 tables:
-
regions (PK: region_id)
-
countries (PK: country_code, FK: region_id)
-
cities (PK: city_id, FK: country_code)
I want
class Region extends CActiveRecord
{
public function relations()
{
return array(
'countries' => array(self::HAS_MANY, 'Country', 'region_id'),
'cities' => array(self::HAS_MANY, 'City', 'country_code', 'through' => 'countries'),
);
}
}
// now get all cities in region with region_id 1
$region = Region::model()->findByPk(1);
$cities = $region->cities;
but this is not working correctly. Yii makes
SELECT
cities.*
FROM cities
LEFT OUTER JOIN countries ON (countries.country_code = cities.city_id)
WHERE countries.region_id = 1
This is nonsense. There is a trick to get it working. Because Yii is changing its procedure for nested through relations, you can get it with
class Region extends CActiveRecord
{
public function relations()
{
return array(
'countries' => array(self::HAS_MANY, 'Country', 'region_id'),
'bugfix' => array(self::HAS_ONE, 'Country', 'country_code', 'through' => 'countries'),
'cities' => array(self::HAS_MANY, 'City', 'country_code', 'through' => 'bugfix'),
);
}
}
First join countries with itself and then cities are a nested through relation. Now $region->cities is what it should be. This is what Yii is doing with the nested through relation - because now PRIMARY_KEY and foreign_key are the same in the first through relation nothing get’s wrong … accidentally.
SELECT
cities.*
FROM cities
LEFT OUTER JOIN countries AS bugfix ON (cities.country_code = bugfix.country_code)
LEFT OUTER JOIN countries AS countries ON (countries.country_code = bugfix.country_code)
WHERE countries.region_id = 1
If you ask me, Relational Query with through has to be rewriten or it will stay unusable. I wonder if anyone ever really understood how it works at the moment. Any comments on this one are highly appreciated.
Keep up the good work.