CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-querysetdjango-orm
Kiran
Kiran

Reputation: 8528

How to multiply 2 columns in Django ORM

I have a MySQL table recording invoice line entries. I would like to multiply the unit_price and quantity to obtain the sub_total(multiple Line Entries). Here is how my line_entries_table looks like

invoice_id| unit_price| quantity    
    14646 | 521.2900  | 1.9000  
    14646 | 200.9900  | 1.5900  
    14646 | 260.0700  | 1.5800  
    14646 | 375.1700  | 1.7100  
    14646 | 496.4300  | 1.8800  
    14646 | 164.3100  | 1.6100  
    14646 | 279.2200  | 1.6400  
    14646 | 343.0100  | 1.7200  
    --------------------------
    25728 | 326.3400  | 1.5300  
    25728 | 521.2900  | 1.9000  
    25728 | 200.9900  | 1.5900  
    25728 | 260.0700  | 1.5800  
    25728 | 375.1700  | 1.7100  
    25728 | 496.4300  | 1.8800  
    25728 | 164.3100  | 1.6100  
    25728 | 279.2200  | 1.6400  
    25728 | 343.0100  | 1.7200  
    25728 | 326.3400  | 1.5300

Result:

invoice_id| sub_total
14646   |   5107.5021
25728   |   2698.8797

I would like to obtain the sub_total of all the invoices at once. Here is the MySQL command which works in my case:

select invoice_id,  SUM(unit_price*quantity) AS sub_total from details_invoice_service_details WHERE invoice_id IN (14646 ,25728) GROUP BY invoice_id

Any idea how to accomplish this in Django:

Here is the part of the code, I tried:

rows = invoice.models.InvoiceLineEntries_Model.objects.filter(invoice_id__in=invoice_ids)

Upvotes: 1

Views: 285

Answers (1)

JPG
JPG

Reputation: 88489

I assume you have a model as below,

class InvoiceLineEntries(models.Model):
    invoice_id = models.IntegerField()
    unit_price = models.FloatField()
    quantity = models.FloatField()

Then, use F(...) expression as,

from django.db.models import F

InvoiceLineEntries.objects.annotate(sub_total=F('unit_price') * F('quantity'))

Upvotes: 2

Related Questions