Doctrine 2: Cannot select entity through identification variables without choosing at least one root entity alias

Question

Rather than an actual question, this is a sticky note for myself, that may help others. There are many other similar questions: 1, 2, 3, 4, 5, 6, but none of them seems to offer this solution.

---

I have the following entities:

class Order
{
    // ...

    /**
     * @ManyToOne(targetEntity="Customer")
     */
    private Customer $customer;

    /**
     * @Column(type="integer")
     */
    private int $amount;
}

class Customer
{
    // ...
}

Order has a unidirectional, many-to-one relationship with Customer. I want to get every customer, along with the total amount of his orders, so I run the following DQL query:

SELECT c, SUM(o.amount)
FROM Model\Order o
JOIN o.customer c
GROUP BY c

But I get the following error:

[Doctrine\ORM\Query\QueryException]
[Semantical Error] line 0, col -1 near 'SELECT c, SUM(o.amount)': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

How can I fix it?

Answer 1

This is a known Doctrine limitation.

The solution is to explicitly SELECT the entity you want to retrieve (Customer) and manually join the other entity (Order) from there, using a WITH condition:

SELECT c, SUM(o.amount)
FROM Model\Customer c
JOIN Model\Order o WITH o.customer = c
GROUP BY c