Return function as a field on django model

Question

I have a model which have a function to calculate the difference between two fields Example:

Class MyModel(models.Model):
    fieldA = models.FloatField()
    fieldB = models.FloatField()

    def delta(self):
        return self.fieldA - self.fieldB

Id like to use this model in a GenericView. I can use the function delta as an extraContext but also id like to include the sum of all Delta results in the template, in this case I have to make an aggregate but again since delta is not a DB Field nor a Model Field I cant use it in an aggregate function.

How can accomplish this?

Answer 1

How could this work? Aggregation is done in the database, but your calculation is obviously in Python.

You'll have to do the sum in Python as well:

aggregated_delta = sum([m.delta() for m in MyModel.objects.all()])

Answer 2

Ok, first your code should have the selfs, since you want to calculate the delta of each line.

def delta(self):
    return self.fieldA - self.fieldB

Regarding your question, it is not possible to aggregate from a django function. There are three ways of achieving what you want:

1. Do the aggregation in Python. Downside: downloads all the data from the database.

2. Bypass the Django ORM, and do the SQL query manually (this turns your code database-specific).

3. Create another field in the database for the delta and update it on save (or with a trigger, if you want to be database-specific).

Answer 3

For what you want just add a new method to the model and do the aggregation on the DB then just calculate the difference between the two sums. Like this:

class MyModel(models.Model):
    fielda = ...
    fieldb = ...
    def delta(self):
        return self.fielda-fieldb
    def aggregate_delta(self):
        return MyModel.objects.aggregate(Sum('fielda')) - MyModel.objects.aggregate(Sum('fieldb'))

Answer 4

One way to do this is to create a Manager and defines a function with the raw SQL query. Create the model object attaching the new calculated field referencing to the model class with self.model

Class MyModel(models.Model):
    fieldA = models.FloatField() 
    fieldB = models.FloatField()
    objects = MyModelManager() #Binds the manager to the model

Class MyModelManager(models.Manager):
    def ReturnDelta(self):
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute = """SELECT fieldA, fieldB, fieldA-fieldB as delta
                            from MyModel_table"""
        result_list = []
    for row in cursor.fetchall():
        p = self.model(fieldA=row[0], fieldB[1]) #access the model associated w/ the manager
        p.delta = row[2] #adds the new field to the queryset
        result_list.append(p)
    return result_list 

Almost a copy-paste from Django Documentation Then we can use the manager function in the extra-context dictionary of the generic view. and do algebraic operations without hitting the database unnecessarily.

A better solution is create your own QuerySet class with a custom Manager. This allows to chain filters and return any computed value as a QuerySet attribute. Is taken almost directly from this snippet

class CustomManager(models.Manager):
    '''
       An general purpose manager which allows to filter a queryset
       and chain filters
       "Constructor": CustomManager(CustomQuerySetClass)
    '''

    def __init__(self, qs_class=models.query.QuerySet):
        super(CustomManager,self).__init__()
        self.queryset_class = qs_class

    def get_query_set(self):
        return self.queryset_class(self.model)

    def __getattr__(self, attr, *args):
        try:
            return getattr(self.__class__, attr, *args)
        except AttributeError:
            return getattr(self.get_query_set(), attr, *args)

class MyModelQuerySet(models.query.QuerySet):
    '''
       A very specific queryset designed to return a computed value (this
       time a sum of all rows values)
    '''
    def filter(self, *args, **kwargs):
        qs = super(GuiaQuerySet, self).filter(*args,**kwargs)
        sum=0
        for row in qs:
            sum += row.delta    #use a callback to prevent from caching
        setattr(qs, 'sum',sum)
        return qs 

class MyModel(models.Model):
    objects = CustomManager()    #Binds the manager to the model
    fieldA = models.FloatField() 
    fieldB = models.FloatField()
    def delta(self):
        return self.fieldA - self.fieldB