0

I'm working with Symfony and Doctrine package. I have two entities Product and ProductLocation that has relation One to Many. I want to get a result of data with all fields from Product and sum of products field from ProductLocation that I can get by aggregation function with group by by productId.

On MySql I'll do query like this:

SELECT p.id, p.name, p.ean13, p.price, p_c.`count`
FROM products p
LEFT JOIN (
    SELECT pl.productId, SUM(pl.location) as `count`
    FROM product_locations pl 
    GROUP BY pl.productId
) p_c ON p_c.productId= p.id

This is my DQL with only Product entity:

  $query =
            $this->createQueryBuilder('p')
                 ->select('p.name, p.ean13, p.price')
                 ->getQuery()
                 ->execute();

But in Doctrine I can only join entities directly, without any subqueries:

->leftJoin('p.productLocation', 'pl')

How can I resolve this problem? Please, help

Oleksandr
  • 1
  • Does this answer your question? [Join subquery with doctrine 2 DBAL](https://stackoverflow.com/questions/34768821/join-subquery-with-doctrine-2-dbal) – Evgeny Ruban Jun 26 '23 at 16:45
  • Trying this `->leftJoin('oos', sprintf('(%s)', $items_location_query->getSQL()), 'il_', 'il_.idProduct = oos.idProduct');` I getting error **[Semantical Error] line 0, col 1405 near 'oos (SELECT p0_.id_product': Error: Class 'oos' is not defined.** But 'oos' - it is an domain alias that exists in query: ` $query = $this->createQueryBuilder('oos')` – Oleksandr Jul 01 '23 at 18:27

0 Answers0