How to get entities in a many-to-many relationship that do NOT have a corresponding linked entity with DQL and Doctrine?

Question

I have a standard many-to-many relationship set up. Entity A can have many of Entity B, and vice versa.

I'm trying to get a list of all Entity A that do NOT have any corresponding Entity B. In SQL, I'd run 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 linking table.

I'm 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'm trying to do?

Answer 1

First, I have to underline that usually you should JOIN on the property of the entity (i.e. s), e.g. instead of:

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

you should have something like:

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

where I'm supposing that in entity A you have 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 stated, if the query is not working yet, then try the following:

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

It is simplest than the previous and also comes from the official docs (i.e. see "phonenumbers example").