Doctrine 2 DQL - 选择多对多字段为空的行? [英] Doctrine 2 DQL - Select rows where a many-to-many field is empty?
问题描述
我想做的是选择所有没有任何国家映射给他们的DeliveryMethod。
我可以做相反的事情,那就是选择至少有一个国家的所有交货方式 -
SELECT m FROM DeliveryMethod m JOIN m.countries
但是我无法弄清楚如何选择国家字段为空的位置。在简单的SQL中,我将执行以下操作(deliverymethod_country是链接表):
SELECT m。* FROM deliverymethods m
LEFT JOIN deliverymethod_country dc ON dc.deliverymethod_id = m.id
WHERE dc.deliverymethod_id IS NULL
但是,与此相反的任何DQL都不起作用,例如:
SELECT m FROM DeliveryMethod m LEFT JOIN m.countries WHERE m.countries IS NULL
哪个给我这个错误:
[语法错误]行0,col 75:错误:字符串的预期结束,获得'm'
这是什么?假设 $ qb
是您的查询构建器实例
$ qb->从('DeliveryMethods','m')中选择('m')
- >
- > leftJoin('m.countries','c')
- > ('COUNT(c.id)= 0')
- > groupBy('m.id');
这将给你与国家相关联的DeliveryMethods,关联国家的数量为0
I have two classes in this example - DeliveryMethod and Country. They have a many-to-many relationship with each other.
What I want to do is select all DeliveryMethods that do not have any Countries mapped to them.
I can do the opposite, that is select all delivery methods that have at least one country -
SELECT m FROM DeliveryMethod m JOIN m.countries
But I can't figure out how to do select where the countries field is empty. In plain SQL I would do the following (deliverymethod_country is the linking table):
SELECT m.* FROM deliverymethods m
LEFT JOIN deliverymethod_country dc ON dc.deliverymethod_id = m.id
WHERE dc.deliverymethod_id IS NULL
However any DQL equivalent of this doesn't work, for example:
SELECT m FROM DeliveryMethod m LEFT JOIN m.countries WHERE m.countries IS NULL
Which gives me this error:
[Syntax Error] line 0, col 75: Error: Expected end of string, got 'm'
What about this? Assuming $qb
is your query builder instance
$qb->select('m')
->from('DeliveryMethods','m')
->leftJoin('m.countries','c')
->having('COUNT(c.id) = 0')
->groupBy('m.id');
This would give you the DeliveryMethods which is associated with countries and count of the associated countries is 0
这篇关于Doctrine 2 DQL - 选择多对多字段为空的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!