CODEWITHSUNDEEP
pythondjango
John Moutafis
John Moutafis

Reputation: 23144

How to execute arithmetic operations between Model fields in django

Prologue:

This is a question arising often in SO:

And can also be applied here:

I have composed an example on SO Documentation but since the Documentation will get shut down on August 8, 2017, I will follow the suggestion of this widely upvoted and discussed meta answer and transform my example to a self-answered post.

Of course, I would be more than happy to see any different approach as well!!

Question:

Assume the following model:

class MyModel(models.Model):
    number_1 = models.IntegerField()
    number_2 = models.IntegerField()
    date_1 = models.DateTimeField()
    date_2 = models.DateTimeField()

How can I execute arithmetic operations between fields of this model?

For example, how can I find:

Upvotes: 8

Views: 11894

Answers (2)

Danny
Danny

Reputation: 1

Step 1: from django.db.models import F

Step 2: from datetime import timedelta

Step 3: You may then apply the F operator in your queryset as follows:

MyModel.objects.all().annotate(
         date_diff=F('date_2') - F('date_1')
     ).filter(date_diff__gte=timedelta(days=10)
)

Upvotes: 0

John Moutafis
John Moutafis

Reputation: 23144

F() expressions can be used to execute arithmetic operations (+, -, * etc.) among model fields, in order to define an algebraic lookup/connection between them.

An F() object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

let's tackle the issues then:

  • The product of two fields:

    result = MyModel.objects.all().annotate(prod=F('number_1') * F('number_2'))

    Now every item in result has an extra column named 'prod' which contains the product of number_1 and number_2 of each item respectively.

  • Filter by day difference:

    from datetime import timedelta

result = MyModel.objects.all().annotate(
             delta=F('date_2') - F('date_1')
         ).filter(delta__gte=timedelta(days=10))

    Now the items in result are those from MyModel whose date_2 is 10 or more days older than date_1. These items have a new column named delta with that difference.

  • A different case:

    We can even use F() expressions to make arithmetic operations on annotated columns as follows:

    result = MyModel.objects.all()
                        .annotate(sum_1=Sum('number_1'))
                        .annotate(sum_2=Sum('number_2'))
                        .annotate(sum_diff=F('sum_2') - F('sum_1'))

Upvotes: 20

Related Questions