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")
* @var Customer
*/
private $customer;
/**
* @Column(type="integer")
* @var int
*/
private $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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments