Querybuilder - Two joins leads to wrong sum results

[Askan Simon]

Hello,

I need to generate reports, this work fine

    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('
            c.category, 
            SUM(s.fulfillable)   AS fulfillable
        ')
        ->from('App\Entity\Catalog', 'c')
        ->leftJoin('App\Entity\Stock', 's', Expr\Join::WITH, 'c.sku = s.sku')
        ->groupBy('c.category')
    ;
 

but when I also want to sum transaction results (multiple), my fulfillable sum is not true anymore

    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('
            c.category, 
            SUM(s.fulfillable) AS fulfillable,
            SUM(t.sold)        AS sold  
        ')
        ->from('App\Entity\Catalog', 'c')
        ->leftJoin('App\Entity\Stock', 's', Expr\Join::WITH, 'c.sku = s.sku')  // Only one Stock
        ->leftJoin('App\Entity\Transaction', 't', Expr\Join::WITH, 'c.sku = t.sku') // Multiple Transactions
        ->groupBy('c.category')
    ;
 

Is there a solution for such cases in Doctrine ORM? Currently I use raw sql querys with subselects ...

I cant split the queries, because a big sortable list is needed.

Thank you & Best Regards

[Marco Pivetta]

On Sun, Jun 9, 2019 at 10:40 PM Askan Simon <askan.s...@gmail.com> wrote:

Is there a solution for such cases in Doctrine ORM? Currently I use raw sql querys with subselects ...

Which is perfectly fine ;-)

Marco Pivetta

http://twitter.com/Ocramius     

http://ocramius.github.com/

[Askan Simon]

Thanks for the answer

I wonder, the reason for missing support for joins on subquerys, is that the results then do not fit to an existing entity/domain?