Howto annotate a QuerySet with calculated properties and use them in ModelAdmin for filtering and sorting ¶
Objectives
- add a calculated property to a Model that can be shown in the admin list_display as a sortable and filterable column
- have the new property 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 method to annotate the queryset, or
- delegate this activity to a custom QuerySet
In both cases, we can optionally override get_queryset() in the manager to have the calculated property available everywhere:
- avantages: the calculated property is 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_progess() method upon explicit request
- use a custom QuerySet, so the new method can be chained with other elaborations 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, Now class 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 property, and add admin_order_field attribute to enable sorting.
from django.db import models from .managers import SomeManager class SomeModel(models.Model): objects = SomeManager() ... #@property def worked_hours(self): # Requires SomeModel.objects.with_progress() return self._worked_hours worked_hours.admin_order_field = '_worked_hours' worked_hours = property(worked_hours) #@property def progress(self): # Requires SomeModel.objects.with_progress() return self._progress progress.admin_order_field = '_progress' progress = property(progress) #@property def ongoing(self): # Requires SomeModel.objects.with_progress() return self._ongoing ongoing.boolean = True ongoing.admin_order_field = '_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: subclass 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, ... ]