Doctrine 2 DQL - 选择多对多字段为空的行？ [英] Doctrine 2 DQL - Select rows where a many-to-many field is empty?

问题描述

在这个例子中，我有两个类 - DeliveryMethod和Country。他们之间有多对多的关系。

我想做的是选择所有没有任何国家映射给他们的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屋！