26

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'
Gnuffo1
  • 3,478
  • 11
  • 39
  • 53

4 Answers4

84

Use Doctrine's is empty

It's specifically designed to check for empty associations:

$qb->select('m')->from('DeliveryMethods', 'm')->where('m.countries is empty')

See: Doctrine 2 ORM Documentation: Doctrine Query Language (search for "is empty")

flu
  • 14,307
  • 8
  • 74
  • 71
23

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

Broncha
  • 3,794
  • 1
  • 24
  • 34
  • Almost perfect, just needed to change ->join() to ->leftJoin() because otherwise it limits the query to methods that have at least one country and returns an empty result. – Gnuffo1 May 09 '12 at 13:04
  • I am having a smiliar problem, maybe you can help? http://stackoverflow.com/questions/14387797/how-to-write-this-query-in-query-builder – sprain Jan 18 '13 at 13:06
  • @Broncha what about one-to-one relations? – parisssss Sep 22 '14 at 19:34
  • @parisssss I guess the same should work in One-To-One as well. On second thought, if the left entity is the owning side, you can just use `$qb->expr()->isNull()` – Broncha Sep 22 '14 at 19:48
  • @Broncha thanks!!it's just worked with `$qb->expr()->isNull()` :) – parisssss Sep 22 '14 at 20:04
  • @Broncha brilliant been struggling with this for hours. – Louwki Mar 30 '15 at 09:14
  • This isn’t working on my end. The other solution submitted by @flu works like a charm – Miles M. Jul 25 '18 at 17:12
  • using a having clause mean that the database have to retrieve the rows and then filter them this is counterproductive. You should use @flu 's solution. – Bruno Nov 26 '18 at 10:43
  • 1
    As stated by @Bruno above, both for readability and performance the 'm.countries is empty' should be used. – vitrus Apr 07 '21 at 10:04
16

There is no need in joins and havings. Simply use SIZE function:

$qb->select('m')
   ->from('DeliveryMethods','m')
   ->where('SIZE(m.countries) = 0');

This will give you all methods without attached countries

pleerock
  • 18,322
  • 16
  • 103
  • 128
  • the better answer, leaves out exceptions where you might need another query because of the 'having'. doctrine will figure out the best strategy, it will for example do an subselect if the relation isnt fetch-joined. – Zaqwsx Dec 03 '15 at 17:04
0

Can't join NULL values, IIRC. Apologies for the typo on twitter, should have said "can't".

spacemonkey
  • 1,616
  • 11
  • 6