Symfony/Doctrine COUNT分组依据和左联接 [英] Symfony / Doctrine COUNT Group By and Left JOIN

问题描述

我通过ManyToOne双向关系在symfony中加入了两个表.文章和日期.

I have two Tables in symfony join by ManyToOne bidirectional relationship. articles and dates.

我想对同一a.id的所有d.quantity求和，我该怎么做?

I would like to sum all the d.quantity for the same a.id How can I do that ?

我尝试:

在我的表(日期)中，我有:

In my table (dates) i have :

+----------------------------
| id   | a.id  |   quantity
+----------------------------
| 1    | 4     |      1 
| 2    | 4     |      3
| 3    | 6     |      4
| 4    | 5     |      6
| 5    | 4     |      15 
----------------------------

$allIndispo = $qb
    ->select('a2.id')
    ->leftJoin('a2.dates', 'd')
    ->where('(a2.quantity - COUNT(d.quantity)) <= 0')
    ->groupBy('d.quantity')
    ->orderBy('a2.id', 'ASC');

推荐答案

您可能需要使用

You probably need to use HAVING instead of WHERE, and group by the a.id, not for quantity, and project (in the select) the result.

$allIndispo = $qb
    ->select('a2.id, SUM(a2.quantity) as total')
    ->leftJoin('a2.dates', 'd')
    ->groupBy('a2.id')
    ->having('(a2.quantity - COUNT(d.quantity)) <= 0')
    ->orderBy('a2.id', 'ASC');

这篇关于Symfony/Doctrine COUNT分组依据和左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！