How to get the Sum of Model Methods from Related Items?

Using Django
1

I already asked a similar question on StackOverflow focused on one aspect, but actually my problem is bigger and I need kind of strategic advice and therefore I think, Django forum is the better place to discuss that.

Actually it’s quite a standard scenario: I have invoices, each with one or many items. Each item has a single net price, a quantity and a tax rate. With that I can calculate the total net price for each item as well as the net price and the gross price for the whole invoice.

(I am talking about several hundreds of invoices per year, not millions and there are very few users, so performance shouldn’t be too high prioritized).

These are the essential parts of my models.py :

from django.db import models
from django.db.models import Sum

class Invoice(models.Model):

    @property
    def price_net(self):
        return # …

    @property
    def price_gross(self):
        return # …

class Item(models.Model):
    invoice = models.ForeignKey(Invoice, on_delete=models.CASCADE)
    quantity = models.DecimalField(blank=False, null=False, max_digits=8, decimal_places=2, default=1)
    price_single_net = models.DecimalField(blank=False, null=False, max_digits=8, decimal_places=2)
    tax_rate = models.PositiveSmallIntegerField(blank=False, null=False, default=DEFAULT_TAX_RATE)

    @property
    def price_total_net(self):
        return self.quantity * self.price_single_net

The view.py is simple:

class InvoiceDetailView(DetailView):
    model = Invoice

In my template invoice_detail.html, this is already working:

{% for item in invoice.item_set.all %}
     {{ item.quantity }}  {{ item.price_single_net }} {{ item.price_total_net }}
{% endfor %}

So far so good. What about {{ invoice.price_net }} and {{ invoice.price_gross }}? I thought it would be a good idea to define that as a Model method as it adds custom “row-level” functionality to my objects, right?

But how can I calculate these values? The problem is, that price_total_net also is a Model method and therefore something like

class Item(models.Model):
    # …

    def price_net(self):
       return self.item_set.aggregate(price_net=Sum('price_total_net'))['price_net']

– which works perfectly for the attribute price_single_net – shows an error: Cannot resolve keyword ‘price_total_net’ into field.

I am stuck at that point. As quite a beginner, I used to pack everything in the view and I am aware that I can aggregate (or annotate?) the data there somehow, but wouldn’t it be better to have a Model method for that?

Is my general approach OK or would you recommend something completely different? Do you have any advice for me?

2

Keep in mind the difference between what happens in the database and what happens in Django. Clauses like Sum work by modifying the SQL that is submitted to the database for the query being executed, but your price_total_net method works on objects after they’ve been returned to Django from the database.

This means you want to calculate your totals in your code, not in the database. For your calculation in the Invoice model, you’ll want to do something like:

def price_net(self):
    return sum([item.price_total_net for item in self.item_set.all()])

… which you can then use in your template as {{ invoice.price_net }}

(Note: there are ways of doing this at the database layer, but you’d have to do all the calculations using the field names, not your Python code - and since you’re going to iterate over all the related objects anyway, there’s not going to be any benefit doing it this way.)

2 Likes
3

Great, Ken, thanks so much for pointing that out! Obviously I couldn’t see the forest for the trees, but I can still take comfort in the fact that I learned a lot of other things in the hours I spent trying to solve the problem myself.

1 Like
5

HI Ken, really appreciate all your responses across this forum. Im wondering why you would not use Aggregate in this case?

6

So there are a couple of aspects to your question that I’d like to address.

First, to directly answer the question you asked - If I were starting from scratch and attempting to solve that problem myself, I would have. However, that’s not the case here. This question was posted with nearly complete code, asking a question about a reference to a single specific field, wondering why it wasn’t working.

From the OP:

It appears to me that my answer was written to address that specific question. I tried to explain why using a Model method wasn’t appropriate in that case, and to show how to get the desired results, with using as much of the provided query and code as practical.

Then, from my parenthetical note at the end of the answer, I apparently determined that given the context in which the entire question was written, there wasn’t anything to be gained by completely rewriting the query. (Especially true considering the parenthetical qualifiers provided as the second paragraph of that original post.)

Having said that, and being quite honest about it, I don’t remember answering this specific question much less what my thinking was when I wrote my answer. So anything I say here, now, comes from trying to reconstruct what my thinking may have been based on what I wrote at the time.

7

Fair enough :slight_smile: it was a while ago, I just wondered why anyone would choose approach over the other. As people espouse different approaches, it is difficult for me to discern the difference between a fix to a best practice because the fix may be easier… Even then its hard to discern which is the fix and which is the best practice lol