Doctrine left join where joined table is null shows results

Question

the goal of this query is to show records from table orders that don't have any shipments created yet. Here is how it should be done in SQL:

SELECT *
FROM orders 
LEFT JOIN orders_shipments shipments ON orders.trx_id = shipments.trx_id
WHERE shipments.shipment_id IS NULL 
AND orders.purchase_date IS NOT NULL 
AND orders.fulfillment_channel = 'MFN';

The following query shows 0 results. Vs the following:

$qb = $this->createQueryBuilder('orders');
        $qb->select('orders, shipments')
        ->leftjoin('orders.shipments', 'shipments')
        ->Where('shipments.id IS NULL')
        ->ANDWhere('orders.purchaseDate IS NOT NULL')
        ->ANDWhere('orders.fulfillmentChannel = :a')->setParameter('a', 'MFN');;
        $results = $qb->getQuery()
        ->getResult();
        return $results;

Does show results. Why is that and how to fix it?

Answer 1

Not sure why but I had to use GROUP and HAVING to get it to work:

$qb = $this->createQueryBuilder('orders');
        $qb->select('orders, shipments')
        ->leftjoin('orders.shipments', 'shipments')
        ->Where('shipments.id IS NULL')
        ->ANDWhere('orders.purchaseDate IS NOT NULL')
        ->ANDWhere('orders.fulfillmentChannel = :a')->setParameter('a', 'MFN')
        ->GroupBy('orders.id')
        ->having('count(shipments) = 0');
        $results = $qb->getQuery()->getResult();