ruby-on-rails - Postgres : Sum from multiple joins resulting in values being more than expected

Question

我有 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')}

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

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

Answer 1

语句生成这个(简化的)查询:

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_notes 中具有相同 invoice_id 的所有行的笛卡尔积，因此 amounts 被多次求和。您应该在单独的派生表(子查询)中计算聚合:

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​​。