Recursive Association Support in CakePHP

We have all been there. A simple query but the query condition is three joins away. Now I don't have to build a large custom query.

After discussing the matter with PHPNut, we decided the method of navigation through the associations should be via :: operators. So to limit users by Country would be

City::State::Area::Country.name

A query using findAll would then be like

$users = $this->User->findAll( array('City::State::Area::Country.name'=>'Canada') );

The query formed would look something like

SELECT `User`.`id`,`User`.`name`,`User`.`email`,`User`.`city_id`, 

`City`.`id`,`City`.`name`,`City`.`state_id`,

`City::State`.`id`,`City::State`.`name`,`City::State`.`area_id`, 

`City::State::Area`.`id`,`City::State::Area`.`name`,`City::State::Area`.`country_id`,

`City::State::Area::Country`.`id`,`City::State::Area::Country`.`name`,`City::State::Area::Country`.`region_id`,

FROM `users` AS `User` 

LEFT OUTER JOIN `cities` AS `City` ON `User`.`city_id`=`City`.`id`

LEFT OUTER JOIN `states` AS `City::State` ON `City`.`state_id`=`City::State`.`id`

LEFT OUTER JOIN `areas` AS `City::State::Area` ON `City::State`.`area_id`=`City::State::Area`.`id`

LEFT OUTER JOIN `countries` AS `City::State::Area::Country` ON `City::State::Area`.`country_id`=`City::State::Area::Country`.`id`

WHERE `City::State::Area::Country`.`name` = 'Canada' 

I have patched dbo_source.php to behave this way. Unfortunately, I have not had the time to work out certain situation where this does not work. The first, and most important, item that does work is the normal CakePHP queries. I have taken great pains to ensure the normal queries work the same with almost no loss of speed.

That said, this technique has only had success with hasOne, hasMany, and belongsTo associations that are in the same database. No external associations and no hasAndBelongsToMany associations work. Once you specify a recursive association using the :: operator, the query will fail if it encounters an external or HABTM association. It also has some issues with the condition being in a string instead of array entries. I will be developing more generic unit tests for this including its deficiencies so that incremental development on them can occur later.

So far these limitations have proved to not be a problem for me and in any case would be solvable using traditional techniques.

Here is the source for version 1.2.0.4451 - dbo_source.php and the unified patch difference dbo_source.patch.

Here is the source for version 1.1.12.4202 - dbo_source.php and the unified patch difference dbo_source.patch.

The dbo_source.php file lives in /cake/libs/model/datasources