CODEWITHSUNDEEP
django
Othman
Othman

Reputation: 3018

Using aggregation with post calculated field

I have a field in a model that will be calculated based on two values. The problem now all my code is done using manual SUM or AVG

What I am trying to do is to have another way to design the model, and in the same time keep the same logic. The main reason I want to do this, because I need to take advantage of the djagno aggregate and annotate functions. Also, to calculate these values in the database side not in Python. 

class Employee(models.Model):
    name = models.CharField(....)
    ..
    salary = models.FloatField(....)

class Benefit(models.Model):
    employee = models.ForeignKey('Employee')

    # this field can be
    # Percentage (ex %2 from employee main salary)
    # Fixed Price (ex $150)
    # days
    calculation_type = thetopchoices(FIX,PER,DAYS)

    # this field should be the value 
    # of the percentage or the fixed amount
    custom = models.PositiveIntegerField(....)

So basically, I have method that will check the calculation type and based on it I get the amount of the benefit. 

def calculate(self):
    if self.calculation_type == 'PER':
        return self.employee.salary * self.custom
    elif self.calculation_type == 'FIX':
        return self.custom
    else
        return ( self.employee.salary / 30 ) * self.custom

@property
def amount(self):
    return self.calculate()

Now the problem how to calculate the total of benefits for each employee. The best solution is to do the calculation in the database by using Django aggregation. However, using this design will never works because Django need the amount to be a real field.

My solution so far, but I don't think it's efficient. 

def get_total_amount_of_benefits(self):
    total = 0
    for b in employee.benefit_set.all():
        total += b.amount

    return total

Upvotes: 0

Views: 169

Answers (1)

Aamir Rind
Aamir Rind

Reputation: 39709

I think you should redefine the Benefit model, add amount field which will be automatically calculated based on calculation_type and custom fields value in model save:

class Benefit(models.Model):
    employee = models.ForeignKey('Employee')

    # this field can be
    # Percentage (ex %2 from employee main salary)
    # Fixed Price (ex $150)
    # days
    calculation_type = thetopchoices(FIX,PER,DAYS)

    # this field should be the value 
    # of the percentage or the fixed amount
    custom = models.PositiveIntegerField(....)
    amount = models.PositiveIntegerField(...., editable=False) # auto calculated field in save

    def __init__(self, *args, **kwargs):
        super(Benefit, self).__init__(*args, **kwargs)
        self.__original_calc_type = self.calculation_type
        self.__original_custom = self.custom

    def calculate(self):
        employee = self.employee
        if self.calculation_type == 'PER':
            amount = employee.salary * self.custom
        elif self.calculation_type == 'FIX':
            amount = self.custom
        else
            amount = ( employee.salary / 30 ) * self.custom
        return amount

    def save(self, *args, **kwargs):
        recalculate = kwargs.pop('recalculate', False)
        if self.__original_calc_type != self.calculation_type or self.__original_custom != self.custom or recalculate:
            self.amount = self.calculate()
        super(Benefit, self).save(*args, **kwargs)
        self.__original_calc_type = self.calculation_type
        self.__original_custom = self.custom

Now to get the amount of all benefits is easy:

from django.db.models import Sum

class Employee(models.Model):
    ...
    ...

    @property
    def benefits_amount(self):
        d = self.benefit_set.aggregate(total_amount=Sum('amount'))
        return d['total_amount'] if d['total_amount'] else 0

If you want to update the benefits later based on any criteria you have, you need to do it like this:

for benefit in employee.benefit_set.all():
    benefit.save(recalculate=True)

Upvotes: 1

Related Questions