CakePHP: How can I query for results based on 2nd level association conditions?

Question

Assume we have 3 models, User, House and Profile with the following associations between them:

• User hasMany House (House belongsTo User)
• User hasOne Profile (Profile belongsTo User)

I would like to query for houses (inside the HousesController class), whose associated Profiles satisfy the given Profile conditions. Consider the following example:

Assuming that gender is a property of Profile model, I would like to retrieve all the houses, whose owner is male.

I have found this question, which is close enough to what I seek for. In my case, the relationship between models is more complex (House belongsTo User hasOne Profile) and I can't get it to work. I have tried something like this, without any luck:

$this->House->find('all', array(
                  'contain' => array(
                      'User' => array(
                          'Profile' => array(
                              'conditions' => array(
                                  'Profile.gender' => 'male'
))))));

The above call returns all the houses and in case the gender is male, it includes the respective User's Profile in the result. Otherwise, the User's Profile remains empty. What exactly I need is to return only the houses whose owner is male.

I have actually implemented it using the 'joins' option of Model::find() function, but I would like to know whether is this possible without using 'joins' and if yes, how?

Answer 1

I would recommend explicitly declaring the relationships using the bindModel method.

You could do this from your Houses controller like so:

/**
 * Bind the models together using explicit conditions
 */
$this->House->bindModel(array(
  'belongsTo' => array(
    'User' => array(
      'foreignKey' => false,
      'conditions' => array('House.user_id = User.id')
    ),
    'Profile' => array(
      'foreignKey' => false,
      'conditions' => array('Profile.user_id = User.id')
    )
  )
));

/**
 * Standard find, specifying 'Profile.gender' => 'male'
 */
$houses = $this->House->find('all', array(
  'conditions' => array(
    'Profile.gender' => 'male'
  )
));