Django Conditional Expressions in Queries

Django Conditional Expressions are added in Django 1.8.
By using Conditional Expressions we can use "If...Elif...Else" expressions  while querying the database.
Conditional expressions executes series of conditions while querying the database, It checks the condition for every record of the table in database and returns the matching results.
Conditional expressions can be nested and also can be combined.

The following are the Conditional  Expressions in Django and Consider the below model for sample queries

class Employee(models.Model):
    ACCOUNT_TYPE_CHOICES = (
        ("REGULAR", 'Regular'),
        ("GOLD", 'Gold'),
        ("PLATINUM", 'Platinum'),
    )
    name = models.CharField(max_length=50)
    joined_on = models.DateField()
    salary = models.DecimalField()
    account_type = models.CharField(
        max_length=10,
        choices=ACCOUNT_TYPE_CHOICES,
        default="REGULAR",
    )

1. WHEN

A When() object is used as a condition inside the query
 

from django.db.models import When, F, Q

>>> When(field_name1_on__gt=date(2014, 1, 1), then="field_name2") # if we want the value in the field
>>> When(field_name1_on__gt=date(2014, 1, 1), then=5) # we can specify external value in place of "5"
>>> When(Q(name__startswith="John") | Q(name__startswith="Paul"), then="name") # we can also use nested lookups

2.CASE

A Case() expression is like the if ... elif ... else statement in Python.  It executes the conditions one by one until one of the given conditions are satisfied. If no conditions are satisfied then the default value is returned if it is provided otherwise "None" will be returned.
 

from django.db.models import CharField, Case, Value, When

>>>ModelName.objects.annotate(
...     field=Case(
...         When(field1="value1", then=Value('5%')),
...         When(field1="value2", then=Value('10%')),
...         default=Value('0%'),
...         output_field=CharField(),
...     ),
... )

If we want to update the account type to PLATINUM  if employee has more than 3 years of experiance, to GOLD if employee has more than 2 years of experiance otherwise REGULAR
For this we can write the code in two ways:
case1: Better Query without using conditional Expressions

>>> above_3yrs = date.today() - timedelta(days=365 * 3)
>>> above_2yrs = date.today() - timedelta(days=365 * 2)

>>> Employee.objects.filter(joined_on__lt=above_3yrs).update(account_type="PLATINUM")

>>> Employee.objects.filter(joined_on__lt=above_2yrs).update(account_type="GOLD")
Above code hits the database for two times to apply the change

case2:  Best Query using  conditional Expressions
 

>>> from datetime import date
>>> above_3yrs = date.today() - timedelta(days=365 * 3)
>>> above_2yrs = date.today() - timedelta(days=365 * 2)
>>> Employee.objects.update(
...     account_type=Case(
...         When(joined_on__lte=above_3yrs,
...              then=Value("PLATINUM")),
...         When(joined_on__lte=above_2yrs,
...              then=Value("GOLD")),
...         default=Value("REGULAR")
...     ),
... )

It hits the database only one time.  So, We can reduce the no of queries to the database. By reducing the number of queries on the database, ultimately we can improve the efficiency and  response time.

    By Posted On
SENIOR DEVELOPER at MICROPYRAMID

Need any Help in your Project?Let's Talk

Latest Comments
Related Articles
Custom Decorators To Check User Roles And Permissions In Django Ramya Ambati

A decorator is a function that takes another function and returns a newer,prettier version of that function.

To know more about decorators in python see ...

Continue Reading...
Django-REST Framework Object Level Permissions and User Level Permissions Chaitanya Kattineni

Django-REST User Level Permissions and Object Level Permissions. User Level Permissions and Object level Permissions allow to serve customers based on their access levels or ...

Continue Reading...
Django Custom Management Command Dinesh Deshmukh

In Django project, We could manage by executing some commands which could be invoked through the manage.py.

For example:
# Sync database
python manage.py syncdb

...

Continue Reading...
open source packages

Subscribe To our news letter

Subscribe and Stay Updated about our Webinars, news and articles on Django, Python, Machine Learning, Amazon Web Services, DevOps, Salesforce, ReactJS, AngularJS, React Native.
* We don't provide your email contact details to any third parties