关系到许多,得到没有这个 [英] Relation to many and get without this
问题描述
用户:
id |名字
1 |一个
2 |两个
3 |三
4 |四
5 |五
房屋:
id |名字
1 |伦敦
2 |巴塞罗那
UserHouse:
id_user | id_house
1 | 1
2 | 2
4 | 1
$ q = Doctrine_Query :: create()
- > from('User u')
//这里有很多选择器
- > leftJoin('u.UserHouse uh')
- > addWhere('????????');
$ users = $ q-> execute();
我想让所有的用户没有House(即 - 不在表UserHouse中) - 这应该返回我用户3和5。
我知道,我可以使用:
和与User的下一个关系,但我必须并使用以下方式:
- > from('User u')
pre>
因为我使用这个查询与许多选择器 - 不能编辑这个。我必须从(用户u)
开始 - >所以我必须填写 - > addWhere('?这个回报给我的用户没有房子?
如果不是与Doctrine,我怎么可以用简单的SQL?
解决方案在SQL中,这种类型的查询需要所谓的
EXCEPTION JOIN
。一些RDBMS实际上将其作为单独的类型(如DB2)实现,而其他RDBMS则需要使用一种解决方法。在您的情况下,这相当于(在SQL中):SELECT用户。*
FROM用户
LEFT JOIN UserHouse
ON UserHouse.id_user = User.id
WHERE UserHouse.id_user IS NULL
哪些将产生预期的不在房子的记录。
这个网站上有很多地方有类似的例子。
我从来没有使用过教义,所以我不能帮你。但是我最好的猜测是这样的:
addWhere('呃IS NULL')
或
addWhere('呃id_user IS NULL')
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?
解决方案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')
这篇关于关系到许多,得到没有这个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!