我有3个表格:发票,发票付款,发票信用注释。我想获取所有总付款额少于发票表中grand_total
金额的发票。
发票模型
has_many :invoice_payments
has_many :invoice_credit_notes
在发票付款模式中
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)
返回的金额高于实际支付的金额。这很可能是因为要联接多个表。
那么,如何克服这个问题?