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.

score 1 · Accepted Answer · edited May 23 '17 at 11:58

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)

edited May 23 '17 at 11:58

Community

1
1

answered Jun 15 '15 at 01:02

Tomaso Albinoni

1,003
1
8
19

Ok, the principle is clear. I was doing this on PHP level using foreach cycles and custom arrays. This is the better way. Thank you! – simPod Jun 15 '15 at 11:14

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

1 Answers1