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", )
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
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
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.
Micropyramid is a software development and cloud consulting partner for enterprise businesses across the world. We work on python, Django, Salesforce, Angular, Reactjs, React Native, MySQL, PostgreSQL, Docker, Linux, Ansible, git, amazon web services. We are Amazon and salesforce consulting partner with 5 years of cloud architect experience. We develop e-commerce, retail, banking, machine learning, CMS, CRM web and mobile applications.
Django-CRM :Customer relationship management based on Django
Django-blog-it : django blog with complete customization and ready to use with one click installer Edit
Django-webpacker : A django compressor tool
Django-MFA : Multi Factor Authentication
Docker-box : Web Interface to manage full blown docker containers and images
More...