CODEWITHSUNDEEP
djangodjango-orm
Miguel Rosales
Miguel Rosales

Reputation: 809

Django ORM complex query

I have the following models:

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

This is a simple Invoice-items structure. I want to be able to distribute a payment among multiple items and keep track of the distribution of the payments. The SQL query provides me with a list of items I can pay and the max amount I can pay. Is there a way to do the same query using pure ORM instead of raw SQL? I'm trying to avoid raw SQL.

Upvotes: 1

Views: 1652

Answers (1)

Vladimir Danilov
Vladimir Danilov

Reputation: 2388

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')

Documentation:

Upvotes: 3

Related Questions