Doctrine ORM: Excluding Records Based on Values of Nested Relationships

Question

Quick Overview

Hey guys! I'm working on an app that has the ability for an Order to contain multiple OrderStatus which then have a relationship with the Status entity themselves. Here's a sort of simplified ERD diagram:

The Question:

What I want to do is get Orders where the last OrderStatus doesn't have a status matching a specific tag, which is a string. So no orders whose last OrderStatus has a Status with a tag of "fulfilled", "pending", etc. I'm pretty much using OrderStatus as a sort of link table because I need to be able to track the time between status changes / keep a history. Otherwise I would have generated it as a many to many relationship.

Simplified Entity Definitions:

Order.php

/**
 * @ORM\Entity(repositoryClass=OrderRepository::class)
 * @ORM\Table(name="`order`")
 */
class Order
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity=OrderStatus::class, mappedBy="cust_order", orphanRemoval=true, cascade={"persist", "remove"}, fetch="EAGER")
     */
    private $orderStatuses;
...

OrderStatus.php

/**
 * @ORM\Entity(repositoryClass=OrderStatusRepository::class)
 */
class OrderStatus
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity=Status::class, fetch="EAGER")
     * @ORM\JoinColumn(nullable=false)
     */
    private $status;

    /**
     * @ORM\ManyToOne(targetEntity=Order::class, inversedBy="orderStatuses")
     * @ORM\JoinColumn(nullable=false)
     */
    private $cust_order;

    /**
     * @ORM\Column(type="datetime")
     */
    private $created;

    public function __construct(Order $order = null, Status $status = null) {
        $this->created = new DateTime("NOW");
        $this->cust_order = $order; 
        $this->status = $status; 
    }...

Status.php

/**
 * @ORM\Entity(repositoryClass=StatusRepository::class)
 */
class Status
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $label;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $tag;

I would appreciate any help I could get! I'm not the most familiar with more complex queries like this. I'm fairly certain this may be resolved somehow with sub queries using the query builder but I can't quite wrap my head around how to go about doing that because I rarely ever have to use sub queries.

Thank you for any help I can get!

Edit: Here's something that I attempted but all this really did was filter out the actual statuses themselves. I need the whole Order to be filtered out:

            $qb
                ->innerJoin("e.orderStatuses", "os")
                ->innerJoin("os.status", "s")
                ->andWhere("s.tag IN (:nonPendingStatuses)")
                ->setParameter("nonPendingStatuses", [
                    "new","in_progress","awaiting_pickup","fulfilled"
                ], \Doctrine\DBAL\Connection::PARAM_STR_ARRAY);

Update

Here is my current attempt. I think I'm close but I don't quite have it yet. Currently getting an array to string conversion error.

        /**
         * Get orders awaiting fulfillment 
         */

         public function getActiveOrders(){
             $qb = $this->repository->createQueryBuilder("o");
            //  $sub_qb = $em->createQueryBuilder();
             $qb-> 
                andWhere(
                    $qb->expr()->notIn(
                        $qb->select('os.id')
                        ->from("App\Entity\OrderStatus", "os")
                        ->andWhere("os.custOrder = o")
                        ->orderBy("os.created", "desc")
                        ->setMaxResults(1)
                        ->getQuery()->getResult()
                        ,
                        $qb
                        ->select('s.id')
                        ->from("App\Entity\Status", "s")
                        ->orWhere("s.tag = 'fulfilled'")
                        ->orWhere("s.tag = 'pending'")
                        // ->andWhere($qb->expr()->in("s.tag",":exclusions"))
                        // ->setParameter(":exclusions", ['pending', 'fulfilled'])
                        ->getQuery()->getResult()
                    )
                )
                ;
                $query = $qb->getQuery();
            return $query->getResult();
         }

Update Here's a SQL query of what I'm trying to implement:

SELECT *
FROM   `order` o
WHERE  (SELECT status_id
        FROM   order_status os
        WHERE  os.cust_order_id = o.id
        ORDER  BY os.created DESC
        LIMIT  1) NOT IN (SELECT id
                          FROM   status s
                          WHERE  s.tag IN ( 'pending', 'fulfilled' )); 

Answer 1

What I want to do is get Orders where the last OrderStatus doesn't have a status matching a specific tag

I assume the latest OrderStatus will be selected based on the higher created column value.

To pick latest OrderStatus for each order we will need greatest-n-per-group logic. To pick only one latest record for each group/order

In pure SQL it can be achieved via window functions or self join

SQL

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')

and in query builder it can written as

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