Making calculations with database columns

Question

I have the following models in django

class Job(models.Model):
    cost = models.FloatField()

class Account(models.Model):
    job = models.ManyToManyField(Job, through='HasJob')

class HasJob(models.Model):
    account = models.ForeignKey(Account, related_name='hasjobs')
    job = models.ForeignKey(Job, related_name='hasjobs')
    quantity = models.IntegerField()

So an Account can have many jobs in different quantities. I want to be able to sum up the total cost of an account. Is that possible in database level or should I do python for it? Like

account = Account.objects.get(pk=1)
sum = 0
for hasjob in account.hasjobs.all():
    sum += hasjob.quantity*hasjob.job.cost

I know its a very "starters" way to do that, and I am guessing it includes many hits on the database. So is there a better way?

Have you looked at this [SO question](http://stackoverflow.com/questions/12165636/django-aggregation-summation-of-multiplication-of-two-fields) which I think might be relevant to what you want to achieve? Also, I think you are going to have to use `F` objects in `.aggregate()` function because `cost` field is defined in another model. So you may want to take a look at this [ticket](https://code.djangoproject.com/ticket/14030). — Ozgur Vatansever, Feb 15 '15 at 10:55

score 1 · Answer 1 · answered Feb 15 '15 at 11:00

IFAIK aggregation can't sum by F() expressions so you have to calculate the sum in python code.

But you can reduce the number of db hits to one - just add the select_related() call to the queryset:

total_sum = sum(hasjob.quantity * hasjob.job.cost
                 for hasjob in account.hasjobs.all().select_related('job'))

Making calculations with database columns

1 Answers1