Snippet

Conditional QuerySet Annotations with Case-When Expressions

Django conditional expressions like Case and When allow performing complex logic directly inside the database query level. Pushing conditional logic into SQL annotations drastically reduces Python memory overhead and improves performance during batch data filtering.

snippet.py

python

from django.db import models
from django.db.models import Case, When, Value, IntegerField
 
class OrderQuerySet(models.QuerySet):
    def with_priority_score(self):
        return self.annotate(
            priority_level=Case(
                When(is_express=True, total_amount__gte=500, then=Value(1)),
                When(is_express=True, total_amount__lt=500, then=Value(2)),
                When(is_express=False, total_amount__gte=500, then=Value(3)),
                default=Value(4),
                output_field=IntegerField()
            )
        )

django

Breakdown

class OrderQuerySet(models.QuerySet):

Defines a custom QuerySet subclass containing reusable domain-specific query methods.

priority_level=Case(

Applies a conditional SQL CASE statement to compute dynamic fields per row in the database.

When(is_express=True, total_amount__gte=500, then=Value(1)),

Evaluates criteria combination and assigns integer rank if both conditions evaluate to true.

output_field=IntegerField()

Explicitly specifies the Django model field data type for the newly generated annotation column.

Previous snippet Next snippet

More Intermediate Python exercises →

Conditional QuerySet Annotations with Case-When Expressions

Related