Doctrine: how to make join with subquery

189 views Asked by At

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

0

There are 0 answers