How to select all rows that don't have one-to-many entity with certain value using Doctrine

Question

I have two entities: Cage and Bird. Cage has Birds inside so their relationship is one-to-many.

Bird has a field name. How can I select all Cages where there's no Bird with name eagle inside.

I was trying to do this:

$cages = $this->createQueryBuilder("c")
            ->leftJoin("c.birds", "b")
            ->where("b.name != :name")
            ->setParameter("name", 'eagle')
            ->getQuery()->getResult();

This works if there's only single Bird (eagle) in the Cage. Then the Cage is not selected which is correct behavior.

But if there are multiple Birds and one of them is eagle, the Cage gets selected even though the eagle is inside.

Answer 1

This is the idea, adapt table and column names as necessary:

SELECT * FROM cages
WHERE cage_id NOT IN
(SELECT cage_id FROM birds WHERE name='eagle');

So, using doctrine:

$qb = $this->createQueryBuilder();

$cagesWithEagles = $qb->select('b.cage_id')
    ->from('birds', 'b')
    ->where("b.name = :name")
    ->setParameter("name", 'eagle')
    ->getQuery()
    ->getResult();

$cagesWithoutEagles = $qb->select('c.cage_id')
    ->from('cages', 'c')
    ->where($qb->expr()->notIn('c.cage_id', $cagesWithEagles))
    ->getQuery()
    ->getResult();

(Inspired by 'where not in' query with doctrine query builder)