CODEWITHSUNDEEP
pythondjangoaggregate-functions
Apostolos
Apostolos

Reputation: 8101

Making calculations with database columns

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?

Upvotes: 3

Views: 76

Answers (1)

catavaran
catavaran

Reputation: 45575

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'))

Upvotes: 1

Related Questions