Building conditional statement across multiple models (CakePHP 1.2.5)

Question

Let's say I have 3 models: User, Region, Country.

User belongsTo Region
Region belongsTo Country

Each of these models is using the Containable behavior. I'm attempting to find users from the country with code 'US'. Here's what I'm attempting:

$users = $this->User->find('all', array(
    'conditions' => array('Country.code' => 'US'),
    'contain'    => array('Region.Country'),
));

CakePHP is separating this into 2 queries:

1. First, it is SELECTing the ID's for all countries with code 'US'.
2. Then it is using those ID's to SELECT all users JOINing regions where region.country_id is in that list of country ID's previously retrieved.

As a result, I end up with SQL errors in my app since my conditions array contains a reference to Country.code, and the second query that Cake builds doesn't JOIN countries.

The best Cake solution I see is to build a sub-query as described in the Complex Find Conditions portion of the manual. However, this seems very convoluted, and is more of a hack than I would like to implement. Is there an easier way that I'm overlooking?

Answer 1

Nate Abele (former lead dev of CakePHP) wrote an article about doing ad hoc joins which might help.

Answer 2

Put the conditions in the contain as well. E.g:

$users = $this->User->find('all', array('contain' => array(
    'Region' => array(
        'Country' => array(
            'conditions' => array('Country.code' => 'US'),
        ),
    ),
)));

See the second-to-last example in the Containable manual