Doctrine 2 DQL - Select rows where a many-to-many field is empty?

doctrine, dql, php

Solution

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

Problem

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

Original source