How-to annotate a Django QuerySet with calculated properties, and use them in ModelAdmin for filtering and sorting (and in other places too)

Mario Orlandi

3 min readMay 6, 2020

Objectives

add one or more calculated properties to a Model
show them in the admin list_display as a sortable and filterable columns
have the new properties available outside the admin
avoid any code duplications

The Manager

This can be achieved in a couple of ways:

using a custom manager with a specific method to annotate the queryset, or
delegate this duty to a custom QuerySet

In both cases, we can optionally override get_queryset() in the manager and invoke the new method to have the calculated properties available everywhere:

avantages: the calculated properties are transparently added to any queryset; no further action is required
disadvantages: the computational overhead occurs even when the calculated properties are not used

In the following example, we opted to:

not override models.Manager.get_queryset()
have the computation available as a separate with_progress() method upon explicit request
use a custom QuerySet, so the new method can be chained with other queryset’s methods in an arbitrary order

import datetime
from django.db import models
from django.db.models import Sum, Case, Q, F, When, Value as V, BooleanField, IntegerField
from django.db.models.functions import TruncDay, Nowclass SomeQuerySet(models.QuerySet):    def with_progress(self):
        return (self
            .annotate(
                _ongoing=Case(
                    When(
                        (Q(start_date=None) |
                         Q(start_date__lte=TruncDay(Now()))) &
                        (Q(end_date=None) |
                         Q(end_date__gte=TruncDay(Now()))) ,
                        then=V('True')
                    ),
                    default=(
                        V(False)
                    ),
                    output_field=BooleanField()
                ),
                _worked_hours=Sum("tasks__hours"),
                _progress=Case(
                    When(
                        hours_budget=0,
                        then=0
                    ),
                    default=(
                        F('_worked_hours') * 100.0 /
                        F('hours_budget')
                    ),
                    output_field=IntegerField()
                ),
            )
        )class SomeManager(models.Manager):    def get_queryset(self):
        return SomeQuerySet(
            model=self.model,
            using=self._db,
            hints=self._hints
        )

The Model

In the model, we wrap every calculated value with a specific Python property,
and add admin_order_field attribute to enable sorting.

Note: we cannot use the @property decorator here, since that would prevent adding any attribute to it. Never mind: just use property() instead.

from django.db import models
from .managers import SomeManagerclass SomeModel(models.Model):    objects = SomeManager()    ...    def worked_hours(self):
        # Requires SomeModel.objects.with_progress()
        return getattr(self, '_worked_hours', '?')
    worked_hours.admin_order_field = '_worked_hours'
    worked_hours.short_description = 'Worked Hours'
    worked_hours = property(worked_hours)    def progress(self):
        # Requires SomeModel.objects.with_progress()
        return getattr(self, '_progress', '')
    progress.admin_order_field = '_progress'
    progress.short_description = 'Progress'    
    progress = property(progress)    def ongoing(self):
        # Requires SomeModel.objects.with_progress()
        return getattr(self, '_ongoing', '?')
    ongoing.boolean = True
    ongoing.admin_order_field = '_ongoing'
    ongoing.short_description = 'Ongoing'
    ongoing = property(ongoing)

The ModelAdmin

In the ModelAdmin, the new properties can be added to list_display to obtain
sortable columns in the changelist, and/or to readonly_fields to show them up in the changeview.

Here, we also override get_queryset() to make sure that the with_progress() is called as required (if we did override SomeManager.get_queryset() instead, this wouldn’t be necessary).

from django.contrib import admin
from .models import SomeModel@admin.register(SomeModel)
class SomeModelAdmin(admin.ModelAdmin):    list_display = [
        ...
        'ongoing',
        'worked_hours',
        'progress',
        ...
    ]
    readonly_fields = [
        ...
        'ongoing',
        'worked_hours',
        'progress',
        ...
    ]    def get_queryset(self, request):
        queryset = (super().get_queryset(request)
            .with_progress()
            .select_related(
                ...
            )
        )
        return queryset

Filtering

To add filtering on a calculated field, we need one more step: subclassing SimpleListFilter as shown below:

class IsOngoingFilter(admin.SimpleListFilter):
    title = 'Ongoing'
    parameter_name = 'dummy'    def lookups(self, request, model_admin):
        return (
            ('Yes', _('Yes')),
            ('No', _('No')),
        )    def queryset(self, request, queryset):
        value = self.value()
        if value == 'Yes':
            return queryset.filter(_ongoing=True)
        elif value == 'No':
            return queryset.exclude(_ongoing=True)
        return queryset

then, in SomeModelAdmin:

@admin.register(SomeModel)
class SomeModelAdmin(BaseModelAdmin):    list_filter = [
        ...
        IsOngoingFilter,
        ...
    ]