CODEWITHSUNDEEP
django
Othman
Othman

Reputation: 3018

Aggregation based on field in Django

I have a model that has a type and value, based on the type I want to change the sign of the number to negative (Without changing it in the database)

class Foo(models.Model):
    EARNING_CATEGORY = 'E'
    DEDUCTION_CATEGORY = 'D'
    CATEGORY_CHOICES = (
        (EARNING_CATEGORY, 'Earning'),
        (DEDUCTION_CATEGORY, 'Deduction'),
    )
    name = models.CharField(max_length=50)
    category = models.CharField(max_length=1, choices=CATEGORY_CHOICES)
    value = models.FloatField()

Now in order to aggregate over Foo I need to consider that values with category = D should be negative so I get the real sum.

What I tried so far is to change the sign during the save() method, but I don't want to show it to the user as negative in the application. So the only way I came up with is to calculate in a method using a for loop. 

class Account(models.Model):
    # feilds

    def sum(self):
        items = self.foo_set.all()
        sum = 0
        for i in items:
            if i.category == Foo.DEDUCTION_CATEGORY:
                sum -= i.value
            else:
                sum += i.value
        return sum

Upvotes: 1

Views: 50

Answers (1)

kaveh
kaveh

Reputation: 2146

You can annotate the current sign during your query like this:

from django.db.models import Case, Value as V, F, FloatField, When

items = self.foo_set.annotate(fixed_value=Case(
    When(category=Foo.DEDUCTION_CATEGORY, then=V('-1')*F('value')),
    default=F('value'),
    output_field=FloatField())
).all()

So during annotation we do a condition check and if our category is equal to 'D', we change sign of value field, and then do your aggregation on fixed_value field.

And here's more info: https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/#case

Upvotes: 3

Related Questions