CakePHP find items with hasOne Model relation that have no associated item

Question

I have a user model which has a hasOne relation with a profile model. I want to find the users which have no profile associated with them. I don't know whether the counterCache works for hasOne relation or there is a better way with appropriate conditions.

Answer 1

I believe that the hasOne relationship creates a LEFT JOIN in the SQL statement. Arguably, the generated SQL for your find would look something like (assume there are more columns though):

SELECT User.id, User.name, Profile.id
FROM users AS User
LEFT JOIN profiles AS Profile
  ON Profile.user_id = User.id;

All you need to do is add another WHERE clause: WHERE Profile.id IS NULL for unmatched rows. Thus changing your Cakephp code to

$this->User->find('all',array(
  'conditions' => array('Profile.id' => 'NULL')))