Django ORM复合查询

Question

我有以下几种型号：

class Invoice(models.Model):
    amount = models.DecimalField()
    date = models.DateTimeField()


class InvoiceItem(models.Model):
    invoice = models.ForeignKey(Invoice)
    description = models.CharField(max_length=256)
    amount = models.DecimalField()

class PaymentDistribution(models.Model):
    item = models.ForeignKey(invoiceItem)
    amount = models.DecimalField()



select a.id,
a.amount,
a.amount-coalesce(sum(b.amount),0) as to_pay
from invoiceitems as a
left join paymentdistribution as b
on (a.id=b.invoiceitem_id)
group by a.id, a.amount
order by to_pay desc

这是一个简单的发票-项目结构.我希望能够在多个项目之间分发付款，并跟踪付款的分布情况。SQL查询为我提供了我可以支付的项目列表和我可以支付的最大金额。是否有一种方法可以使用纯ORM而不是原始SQL执行相同的查询？我试图避免使用原始SQL。

Answer 1

from django.db.models import F, Sum, Value as V
from django.db.models.functions import Coalesce

InvoiceItem.objects.values('id', 'amount').annotate(
    to_pay=F('amount') - Coalesce(Sum('paymentdistribution__amount'), V(0))
).order_by('-to_pay')

文档：

• 聚合
• 表达
• 数据库功能