Doctrine 2 DQL - 选择多对多字段为空的行?

2022-01-16 00:00:00 php doctrine dql

在这个例子中我有两个类 - DeliveryMethod 和 Country.它们之间是多对多的关系.

I have two classes in this example - DeliveryMethod and Country. They have a many-to-many relationship with each other.

我想要做的是选择没有任何国家映射到它们的所有 DeliveryMethods.

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

但我不知道如何选择国家字段为空的位置.在普通 SQL 中，我会执行以下操作(deliverymethod_country 是链接表):

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

但是任何与此等效的 DQL 都不起作用，例如:

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

这给了我这个错误:

[Syntax Error] line 0, col 75: Error: Expected end of string, got 'm'

推荐答案

这个呢?假设 $qb 是您的查询构建器实例

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

这将为您提供与国家/地区关联的 DeliveryMethods，关联国家/地区的数量为 0

This would give you the DeliveryMethods which is associated with countries and count of the associated countries is 0

相关文章