STAT relation over 2 nested relations

Yii 1.1 General Discussion
1

Suppose I have the three following simple tables in my database:

[font="Courier New"]country

id int auto_increment

name varchar(255)

region

id int auto_increment

countryId int

name varchar(255)

city

id int auto_increment

regionId int

name varchar(255)

habitants int[/font]

The relation country:region is 1:many and the relation region:city is also 1:many

Now suppose I want to know what the number of habitants in the city with the smallest number of habitants is, given a country. Note that I’m ~not~ interested in which city it is, just the number of habitants.

Because a query says more than a thousand words:




SELECT

   country.id

 , min(habitants)

FROM

   country

    INNER JOIN region

     ON country.id=region.countryId

    INNER JOIN city

     ON region.id=city.regionId

GROUP BY

   country.id

How can I do this with Yii’s relations? I was thinking I could use a STAT relation combined with Yii 1.1.7’s new ‘through’, but ‘though’ is not defined for CStatRelation. Then I was thinking I could use ‘with’, but that’s also not supported on CStatRelation. Then I tried a HAS_MANY relation on country with a ‘though’ over region to city and a ‘select’=>‘MIN(habitants)’ but it didn’t like the ‘MIN(habitants)’ part (Active record “City” is trying to select an invalid column “MIN(price)”. Note, the column must exist in the table or be an expression with alias.), so now I’m kind of out.

I realize can use DAO to get what I want, but I would like to do this as a relation so I can also sort on it, and because I think it should be possible :)

So, is there any way I can do this with relations or am I just plain out of luck here?

2

Not tested, but you probably can build the query using select,join and group properties of CStatRelation:




'minHabitants' => array(

	self::STAT,

	'Region',

	'countryId',

	'select' => 'MIN(city.habitants)',

	'join' => 'INNER JOIN city ON region.id = city.regionId',

)

Sorry, no need to group, it’s a Stat relation.

3

Yes, that works! I had to make one minor adjustment though; on the last line, instead of region.id, it should be t.id (region.id doesn’t exist within the scope of this query since Yii assigns the alias t to the table region)




'minHabitants' => array(

   self::STAT,

   'Region',

   'countryId',

   'select' => 'MIN(city.habitants)',

   'join' => 'INNER JOIN city ON t.id = city.regionId',

)

Thanks mate 8)