Does function order matter in Doctrine2 Query Builder?

Question

I have two entities Order and Shipment. Each Order has one Shipment and visa versa. I am trying to get a query of Orders that are pending. Here are the criteria:

• The Order has been sent (and not just saved)
• The Order has not been cancelled
• The Order doesn't have a Shipment
• OR The Order has a Shipment
  • But the Shipment has not been sent (it was just saved)
  • If the Shipment has been sent, it also has been cancelled

Here is the query I have come up with:

<?php

use Doctrine\ORM\EntityRepository;

class OrderRepository extends EntityRepository
{

    public function findPending($id)
    {
        return $this->createQueryBuilder('o')
            ->addSelect('s')
            ->leftJoin('MyApp\\Model\\Entity\\Shipment', 's')
            ->orderBy('o.date_sent', 'DESC')

            // Order has been sent
            ->where($qb->expr()->neq('o.date_sent',':date_sent'))
            ->setParameter('date_sent', '0000-00-00 00:00:00')

            // Order was not cancelled
            ->where($qb->expr()->eq('o.date_cancelled',':date_cancelled'))
            ->setParameter('date_cancelled', '0000-00-00 00:00:00')

            // Order does not have a shipment
            ->andWhere($qb->expr()->isNull('s.order'))

            // OR Shipment has not been sent
            ->orWhere($qb->expr()->neq('s.date_sent', ':ship_date_sent'))
            ->setParameter('ship_date_sent', '0000-00-00 00:00:00')

            // AND Shipment has not been cancelled
            ->andWhere($qb->expr()->eq('s.date_cancelled',':ship_date_cancelled'))
            ->setParameter('ship_date_cancelled', '0000-00-00 00:00:00')

            ->setMaxResults(6)
            ->getQuery()
            ->getResult();
    }
}

It seems to be working, but I don't have much data to test it with. I am worried about the last ->andWhere() statement, to see if the Shipment has not been cancelled. If I use 'and', I am worried it will only return Orders with Shipments that "have not been sent and have not been cancelled" instead of "have not been sent or if sent, has not been cancelled". If I change the ->andWhere() to ->orWhere() I assume it will return Orders with Shipments that "have been sent but are not cancelled".

My main concern is how the order of the query functions effect the query. Also, should where() only be used once? I don't see the difference between where() and andWhere()?

If my question isn't clear enough, let me know and I will update it.

Thank you in advance.

Update

I have dug a little deeper and come up with this new query. Which I think would work?

public function findPending($id)
{
    $qb = $this->createQueryBuilder('o')
        ->addSelect('s')
        ->leftJoin('MyApp\\Model\\Entity\\Shipment', 's')
        ->orderBy('o.date_sent', 'DESC')

        // Order has been sent and was not cancelled
        ->where($qb->expr()->andX(
            $qb->expr()->eq('o.date_cancelled','0000-00-00 00:00:00'),
            $qb->expr()->neq('o.date_sent','0000-00-00 00:00:00')
            ))

        ->andWhere($qb->expr()->orX(
            // Order doesn't have a shipment
            $qb->expr()->isNull('s.order'),
            // OR Order has a shipment
            $qb->expr()->orX(
                // Shipment has not been sent
                $qb->expr()->eq('s.date_sent','0000-00-00 00:00:00'),
                // OR Shipment has been sent AND it was cancelled
                $qb->expr()->andX(
                    $qb->expr()->neq('s.date_sent','0000-00-00 00:00:00'),
                    $qb->expr()->eq('s.date_cancelled','0000-00-00 00:00:00')
                    )
                )
            ))

        ->setMaxResults(6)
        ->getQuery()
        ->getResult();
    return $qb;
}

Answer 1

yes, where can only be used once, so if the andWhere (or orWhere) are conditional but could be used multiple times, you should probably use where 1=1