时间戳

首页 > 解决方案 > Postgres：来自多个连接的总和导致值超出预期

ruby-on-rails - Postgres：来自多个连接的总和导致值超出预期

问题描述

我有 3 个表：invoices、invoice_payments、invoice_credit_notes。我想获取所有支付总额小于grand_total发票表中金额的发票。

在发票模型中

has_many :invoice_payments
has_many :invoice_credit_notes

在 invoice_payments 模型中

belongs_to :invoice

在 invoice_credit_notes 模型中

belongs_to :invoice

invoice_payments有amount和discount领域。

invoice_credit_notes里面有amount字段。

这是我想出的：

scope :unpaid, ->{left_outer_joins(:invoice_payments, :credit_note_invoices)
                  .having('(COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(credit_note_invoices.amount), 0)) < ROUND(invoices.grand_total, 0)')
                  .group('invoices.id')}

但是我的解决方案存在问题，因为可能有多个 invoice_payments 或 invoice_credit_notes(COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0)返回的金额高于实际支付的金额。这很可能是因为正在连接多个表。

那么，如何克服这个问题呢？

标签： ruby-on-railspostgresqlactiverecord

解决方案

该语句生成这个（简化的）查询：

select sum(p.amount+ p.discount)+ sum(n.amount)
from invoice i
left join invoice_payments p on p.invoice_id = i.id
left join invoice_credit_notes n on n.invoice_id = i.id
group by i.id;

这两个连接创建了所有行的笛卡尔积，因此invoice_payments被多次求和。您应该在单独的派生表（子查询）中计算聚合：invoice_credit_notesinvoice_idamounts

select p.sum+ n.sum
from invoice i
left join (
    select invoice_id, sum(amount+ discount)
    from invoice_payments
    group by invoice_id
    ) p on p.invoice_id = i.id
left join (
    select invoice_id, sum(amount)
    from invoice_credit_notes
    group by invoice_id
    ) n on n.invoice_id = i.id

我希望您可以轻松地将其翻译成 ruby​​。

推荐阅读