Brainstorm's snippets (1/256)

  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,
        ...
    ]