Restrict query results based on attributes of 1 item in 1-n association

Question

I have an entity 'Order', and a one-to-many associated entity 'OrderStatus' (so 1 order can have many statuses). The current status of an order is defined by the last status that was added to that order.

Now I want to create a DQL query which selects all orders with a certain status. However, because this is a one-to-many relation, I have no idea how to accomplish this in DQL. I only know of querying the collection of statuses as a whole.

Does anyone have an idea if this is even possible, or do I have to use a workaround?

Answer 1

We had this very same problem in a project we're working on. The query you're trying to perform is very similar to this question, except that you're trying to do this in DQL, which makes it even harder. I think (but I might be wrong) that DQL does not allow this kind of query, and you may achieve the result you're expecting with a native SQL query, with all the caveats this implies.

What we ended up with, and I strongly suggest to you, is to make the current status a property of your Order. This allows easy & fast querying, with no joins required. The change is really painless:

class Order
{
    /**
     * @ManyToOne(targetEntity="OrderStatus")
     */
    protected $status;

    /**
     * @OneToMany(targetEntity="OrderStatus")
     */
    protected $statuses;

    public function setStatus($status)
    {
        $orderStatus = new OrderStatus($this, $status);
        $this->statuses->add($orderStatus);
        $this->status = $orderStatus;
    }
}

$status can also be a simple string property, if your OrderStatus is basically composed of a status string and a date; the code would then become:

class Order
{
    /**
     * @Column(type="string")
     */
    protected $status;

    // ...

    public function setStatus($status)
    {
        // ...
        $this->status = $status;
    }
}