Doctrine ORM: Excluding Records Based on Values of Nested Relationships



PHP Snippet 1:

SELECT o.*
FROM `order` o
JOIN order_status os ON o.id = os.cust_order_id
LEFT JOIN order_status os1 ON os.cust_order_id = os1.cust_order_id
                           AND os.created < os1.created
JOIN STATUS s ON s.id = os.status_id
WHERE os1.created IS NULL
AND s.tag NOT IN ('pending')

PHP Snippet 2:

$this->createQueryBuilder('o')
    ->innerJoin("o.orderStatuses", "os")
    ->innerJoin("os.status", "s")
    ->leftJoin(
        'Bundle\Entity\OrderStatus',
        'os1',
        'WITH',
        'os.cust_order = os1.cust_order AND os.created < os1.created'
    )
    ->where('os1.created IS NULL')
    ->andWhere("s.tag NOT IN (:pendingStatus)")
    ->setParameter("pendingStatus", ["pending"], \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
    ->getQuery()
    ->getResult();