php - Mysql( Doctrine )正在重复计数结果，可能是由于分组依据，但无法弄清楚

Question

我使用 Doctrine 作为 ORM 层，但是通过在我的数据库工具中放置一个普通的 mysql 查询，我得到了相同的结果。所以我的问题是:

我有一个发票表、发票项目和发票付款表，我希望得到的结果是所有未付款或至少尚未全额付款的发票。我知道查询应该几乎是正确的，因为它返回了正确数量的项目...唯一的事情是它将该数量乘以可能连接的行数。

所以我现在的查询:

select  invoice.*, sum(item.amount * item.quantity) as totalDue, 
        sum(payment.amount) as totalPaid 
from invoices as invoice 
    left join invoice_items as item on item.invoice_id = invoice.id 
    left join invoice_payments as payment on payment.invoice_id = invoice.id 
        and payment.status = 'successful' 
where invoice.invoice_number is not null 
and invoice.sent_at is not null 
and invoice.due_date >= '2018-05-15' 
group by invoice.id 
having count(payment.id) = 0 
    or sum(payment.amount) < sum(item.amount * item.quantity) 
order by invoice.issue_date desc, sum(payment.amount) desc;

正如您所看到的，我的选择中还有totalDue 和totalPaid(这些仅供引用，如果查询正确，则应将其删除)。

我看到的是金额乘以六(因为付款表中有 6 个项目)。

所以也许有人可以帮助我指出正确的方向，它不会对totalDue进行乘法运算。我想这可能是因为 group by 但没有我的查询失败了。

Answer 1

通过在查询中简单地使用不同的值，我解决了这个问题。

select invoice.*, sum(distinct(item.amount * item.quantity)) as totalDue, 
        sum(payment.amount) as totalPaid 
from invoices as invoice 
    left join invoice_items as item on item.invoice_id = invoice.id 
    left join invoice_payments as payment on payment.invoice_id = invoice.id 
        and payment.status = 'successful' 
where invoice.invoice_number is not null 
and invoice.sent_at is not null 
and invoice.due_date >= '2018-05-15' 
group by invoice.id 
having count(payment.id) = 0 
    or sum(payment.amount) < sum(distinct(item.amount * item.quantity)) 
order by invoice.issue_date desc, sum(payment.amount) desc;

我要感谢所有花时间重新设计我的问题的人;-)