Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
334 views
in Technique[技术] by (71.8m points)

php - Relation to many and get without this

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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')

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...