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.
Assume you have the models User, City, State, Area, Country, Region. Users are in a City. Cities are in a State. States are in an Area. Areas are in a Country. Countries are in a Region. How do you query for Users in a Country? Or Area? The query is simple but Cake has not supported it. This feature is listed as issue #633 in Trac.
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.
(EDIT: I supported this method through version 1.1.17. I now have a much better solution for CakePHP version 1.2 that requires no modification core. See blog entry )
