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:
number_1
and number_2
of a MyModel object?date_2
is 10 or more days older than date_1
?Inside the Python loop, we are performing arithmetic operations on elements of the first and second lists.
Mine is simpler to implement, and you can pass a list, dict, or anything that can be converted into json. In Django 1.10 and above, there's a new ArrayField field you can use.
In Django, the model is the object mapped to the database. When you create a model, Django executes SQL to create a corresponding table in the database (Figure 4-2) without you having to write a single line of SQL. Django prefixes the table name with the name of your Django application.
A field is thus a fundamental piece in different Django APIs, notably, models and querysets. In models, a field is instantiated as a class attribute and represents a particular table column, see Models. It has attributes such as null and unique, and methods that Django uses to map the field value to database-specific values.
Also, you can easily write your own custom model fields. Technically, these models are defined in django.db.models.fields, but for convenience they’re imported into django.db.models; the standard convention is to use from django.db import models and refer to fields as models.<Foo>Field. The following arguments are available to all field types.
The only field included with Django where this is True is ManyToManyField. Boolean flag that is True if the field has a many-to-one relation, such as a ForeignKey; False otherwise. Boolean flag that is True if the field has a one-to-many relation, such as a GenericRelation or the reverse of a ForeignKey; False otherwise.
An F () expression is a way for Django to use a Python object to refer to the value of model field or annotated column in the database without having to pull the value into Python memory. This allows developers to avoid certain race conditions and also filtering results based on model field values.
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'))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With