CODEWITHSUNDEEP
phpsqlcakephpjoinleft-join
Matt Huggins
Matt Huggins

Reputation: 83289

Building conditional statement across multiple models (CakePHP 1.2.5)

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?

Upvotes: 0

Views: 1426

Answers (2)

Sander Marechal
Sander Marechal

Reputation: 23216

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

Upvotes: 1

neilcrookes
neilcrookes

Reputation: 4203

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

Upvotes: 2

Related Questions