Django - show sums of a table of records

I have a table which has a list of invoices and their details:

class Invoice(models.Model):
    invoiceNum = models.CharField(etc...)
    invoiceDate = models.DateField(etc...)
    customerID = models.ForeignKey(etc...)
    isPaid = models.CharField(etc...)

The Invoice records do not hold the actual invoice total. Instead, an invoice's total is made up of several Invoice_Line_Item records, held in another table:

class Invoice_Line_Item(models.Model):
    invNum = models.ForeignKey(Invoice)
    itemName = models.CharField(etc...)
    itemPrice = models.DecimalField(etc...)

I have a webapp that shows all the invoices in a big HTML table, along with all the details of that invoice on the table's tr row. Details such as, Invoice Date, Invoice Number, Customer ID, all come from that Invoice table. There are hundreds of invoices to display in this HTML table.

What I would like to do is also show each invoice's total value - which is the sum of all the line items. However, I can't think of a simple way to acomplish this since the invoice details and the line items that make up the invoice's total are in two different tables.

One way I thought is to pass the entire Invoice_Line_Item querySet into the HTML template, then for each invoice displayed in a table tr, I could iterate over the entire Inv开发者_StackOverflowoice_Line_Item querySet, adding up all the line items that match the current invoice. This, however, seems hugely inefficient.

Any better ideas on how to do this?

Thanks!

One word: Aggregation

Invoice_Line_Item.objects.filter(invNum=invoice).aggregate(Sum('itemPrice'))

https://docs.djangoproject.com/en/dev/topics/db/aggregation/

Another way is to store the total in Invoice and update it whenever you change a related Invoice_Line_Item

One more word: annotate.

from django.models import Sum

Invoice.objects.filter( .. ).annotate( InvTotal=Sum( 'invoice_line_number__itemPrice' ) ) 

InvTolal becomes a new attribute of Invoice object, you can use it in template the same way as invoiceNum or invoiceDate.

With this approach you do not have to pass any additional data structures to your template, only a QuerySet of Invoices.

Please note: Argument of Sum is a string, which is a concatenation of the name of related model converted to lowercase, than double '_', and than the name of a field in related model.