Can I get this result of PostgreSQL query using Django ORM?

Question

I am having trouble with django ORM. I want to get the data which I can get using PostgreSQL and raw sql query in views. But is there any solution to achieve this using Django ORM. Here is my models

class Invoice(models.Model):
    class PaymentMode(models.TextChoices):
        Cash = 0, _('CASH')
        Credit = 1, _('CREDIT')

    class PaymentStatus(models.TextChoices):
        Paid = 0, _('PAID')
        Pending = 1, _('PENDING')

    total_amount = models.IntegerField()
    payment_mode = models.CharField(choices=PaymentMode.choices,
                                    max_length=20,
                                    default=PaymentMode.Credit)
    payment_status = models.CharField(choices=PaymentStatus.choices,
                                      default=PaymentStatus.Pending,
                                      max_length=15)
    print_date = models.DateField(default=now)
    invoice_date = models.DateField(default=now)
    created_by = models.ForeignKey(User, on_delete=models.DO_NOTHING)
    customer = models.ForeignKey(Customer, on_delete=models.DO_NOTHING)


class Payment(models.Model):
    class PaymentMethod(models.TextChoices):
        Cash = 0, _('CASH')
        Cheque = 1, _('CHEQUE')
        OnlineTransfer = 2, _('WEB_TRANSFER')
        Other = 3, _('OTHER')

    invoice = models.ForeignKey(Invoice,
                                on_delete=models.DO_NOTHING,
                                default=0)
    amount = models.IntegerField()
    date = models.DateField(default=now)
    recieved_by = models.CharField(max_length=150)
    payment_method = models.CharField(choices=PaymentMethod.choices,
                                      default=PaymentMethod.Cash,
                                      max_length=20)

and here is my PostgreSQL query

SELECT 
    A.id ,
    A.total_amount ,
    A.payment_mode,
    A.payment_status,
    A.print_date,
    A.invoice_date,
    A.created_by_id,
    A.customer_id,
    coalesce(SUM(P.amount), 0) AS "paid",
    (A.total_amount - coalesce(SUM(P.amount), 0)) As "remaining"
    FROM
        public."Invoicing_invoice" as A
    LEFT JOIN public."Invoicing_payment" as P
        ON P.invoice_id = A.id   
    GROUP BY A.id

willeM_ Van Onsem · Accepted Answer · 2021-03-25 18:44:43Z

2

You can annotate with:

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

Invoice.objects.annotate(
    paid=Coalesce(Sum(payment__amount), Value(0)),
    remaining=F('total_amount') - Coalesce(Sum(payment__amount), Value(0))
)

The Invoices that arise from this queryset will have two extra attribute .paid and .remaining with the paid and remaining amount of the invoice.

answered Mar 25, 2021 at 18:44

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can I get this result of PostgreSQL query using Django ORM?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related