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