Querybuilder - Two joins leads to wrong sum results

21 views
Skip to first unread message

Askan Simon

unread,
Jun 9, 2019, 4:40:56 PM6/9/19
to doctrine-user

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

unread,
Jun 9, 2019, 7:42:39 PM6/9/19
to doctri...@googlegroups.com
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 ;-)

Askan Simon

unread,
Jun 10, 2019, 3:43:12 AM6/10/19
to doctrine-user
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?
Reply all
Reply to author
Forward
0 new messages