CODEWITHSUNDEEP
symfonydoctrine-ormdoctrine
user1029829
user1029829

Reputation: 961

Doctrine left join where joined table is null shows results

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?

Upvotes: 6

Views: 9992

Answers (1)

user1029829
user1029829

Reputation: 961

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();

Upvotes: 10

Related Questions