How to get objects in a many-to-many relationship that do not have an associated object with DQL and Doctrine?

Question

I have a standard many-to-many relationship. An object A can have many Entity B objects and vice versa.

I am trying to get a list of all Entity A that DO NOT have the corresponding Entity B. In SQL, I would execute a query like this:

SELECT a.* FROM entity_a a LEFT JOIN a_b r ON r.AID = a.id WHERE r.BID IS NULL 

In this query, a_b is the binding table.

I am trying to write a DQL statement (or use some other method) to get the same result, but the following does not work:

 SELECT s FROM VendorMyBundle:EntityA s LEFT JOIN VendorMyOtherBundle:EntityB u WHERE u IS NULL 

How can I achieve what I am trying to do?

Answer 1

First, I must emphasize that usually you should JOIN an object property (i.e. s ), for example. instead:

 SELECT s FROM VendorMyBundle:EntityA s LEFT JOIN VendorMyOtherBundle:EntityB u WHERE u IS NULL 

you should get something like:

 SELECT s FROM VendorMyBundle:EntityA s LEFT JOIN s.mylistofb u WHERE u IS NULL 

where I assume that in entity A you defined your relationship as:

 class A{ // ... /** * @ManyToMany(targetEntity="Vendor\MyBundle\Entity\EntityB") * @JoinTable(name="as_bs", * joinColumns={@JoinColumn(name="a_id", referencedColumnName="id")}, * inverseJoinColumns={@JoinColumn(name="b_id", referencedColumnName="id", unique=true)} * ) **/ private $mylistofb; 

This is indicated if the request does not work yet, try the following:

 SELECT s FROM VendorMyBundle:EntityA s WHERE SIZE(s.mylistofb) < 1 

This is the simplest than the previous one, as well as from official white papers (i.e. see phonenumbers example).