Relation to many and get without this

Question

User:
id | name
1  | one
2  | two
3  | three
4  | four
5  | five


House:
id | name
1  | London
2  | Barcelona

UserHouse:
id_user | id_house
 1      |  1
 2      |  2
 4      |  1


$q = Doctrine_Query::create()
  ->from('User u')
   // many selectors here
  ->leftJoin('u.UserHouse uh')
  ->addWhere('????????');

$users = $q->execute();

I would like get all user without House (that is - not in table UserHouse) - this should return me user 3 and 5.

I know, i can use:

->from('UserHouse uh')

and next relation to User but i must have and use:

  ->from('User u')

because i use this query with many selectors - can't edit this. I must started with ->from('User u')

So what i must fill in ->addWhere('????????') that this return me users without house?

If not with Doctrine, how can i get this with simply SQL?

Answer 1

In SQL, this type of query needs what is known as an EXCEPTION JOIN. Some RDBMSs actually implement this as a separate type (such as DB2), while others need to use a workaround. In your case, it amounts to (in SQL):

SELECT User.* 
FROM User
LEFT JOIN UserHouse
ON UserHouse.id_user = User.id
WHERE UserHouse.id_user IS NULL

Which will yield the expected 'not in a house' records.

There are similar examples in a number of places on this site.

I've never used Doctrine, so I can't help you there. But my best guess would be something like:

addWhere('uh IS NULL')

or

addWhere('uh.id_user IS NULL')