The django-filter package adds a quick way to add URL parameter driven filtering to existing Django database models. For example, take the following model:

from django.db import models

class Purchase(models.Model):
    item = models.TextField()
    amount = models.FloatField()
    at = models.DateTimeField()

    class Meta:
        db_table = "purchases"

We can then use django-filter to quickly create a FilterSet class that wraps the above database model:

import django_filters
from .models import Purchase

class PurchaseFilter(django_filters.FilterSet):
    item = django_filters.CharFilter(lookup_expr="iexact")
    sort = django_filters.OrderingFilter(
        fields=(
            ("item", "item"),
            ("amount", "amount"),
            ("at", "at"),
        ),
    )

    class Meta:
        model = Purchase
        fields = ("amount", "at")

The above can then be integrated into views, such as ListView:

from django.views.generic.list import ListView
from .filters import PurchaseFilter

class Purchases(ListView):
    def get_queryset(self, **kwargs):
        return PurchaseFilter(self.request.GET).qs

This configuration allows GET parameters to control the filtering and ordering of database queries in views with a minimal amount of glue code. For instance, if the above Purchases view is at the /purchases URL, requesting /purchases?item=lunch&sort=amount will generate the following SQL:

SELECT "purchases"."id", "purchases"."item", "purchases"."amount", "purchases"."at"
FROM "purchases"
WHERE "purchases"."item" LIKE 'lunch'
ORDER BY "purchases"."amount";

The FilterSet can be extended by overriding its qs property. For instance, to aggregate the results by item and sum their amounts:

class AggregatedPurchaseFilter(django_filters.FilterSet):
    item = django_filters.CharFilter(lookup_expr="iexact")
    sort = django_filters.OrderingFilter(
        fields=(
            ("item", "item"),
            ("total", "total"),
        ),
    )

    @property
    def qs(self):
        return super().qs.values("item").annotate(total=models.Sum("amount"))

    class Meta:
        model = Purchase
        fields = ("item",)

The above produces a familiar GROUP BY statement in SQL:

SELECT "purchases"."item", SUM("purchases"."amount") AS "total"
FROM "purchases"
GROUP BY "purchases"."item";

However, when attempting to sort the filter by total using the URL sort parameter, the following error is returned:

FieldError
Cannot resolve keyword 'total' into field. Choices are: amount, at, id, item

Order Matters

This error originates from the Django ORM query planner when the parent FilterSet class attempts to add ordering to the queryset. In brief, the query planner is being told to order by a field that has not yet been created! We can reproduce the same error without django-filter using the following query:

Purchase.objects.order_by("total").values("item").annotate(total=models.Sum("amount"))

Developers familiar with SQL might consider this an obvious mistake. Of course order matters! SQL is fairly strict about its syntax and placing an ORDER BY clause before GROUP BY would result in a syntax error. The Django ORM, however, abstracts query construction, and in our examples above, that construction is spread across several classes and their sub-classes.

To fix our FieldError issue, we need to aggregate the queryset before it is passed to the FilterSet. Thankfully, the FilterSet includes an optional queryset parameter that does exactly this:

queryset = Purchase.objects.values("item").annotate(total=models.Sum("amount"))
PurchaseFilter(request.GET, queryset=queryset)

Which produces the expected SQL without issue:

SELECT "purchases"."item", SUM("purchases"."amount") AS "total"
FROM "purchases"
GROUP BY "purchases"."item"
ORDER BY "total";

Proxy Models

Unfortunately, having to filter the queryset before initializing the FilterSet adds a considerable amount of glue code to our application and prevents us from writing views with modular filters that can be specified by class.

Thankfully, we can also aggregate our queryset before the FilterSet via proxy models and custom managers. We can add the following alongside our Purchase class:

class AggregatedPurchaseManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().values("item").annotate(total=models.Sum("amount"))


class AggregatedPurchase(Purchase):
    objects = AggregatedPurchaseManager()

    class Meta:
        proxy = True

The above proxy model allows us to change the default queryset without additional database tables. We also no longer need to extend the FilterSet queryset or pass the filtered queryset to it during initialization. We only need to change its model property to our proxy:

class AggregatedPurchaseFilter(django_filters.FilterSet):
    item = django_filters.CharFilter(lookup_expr="iexact")
    sort = django_filters.OrderingFilter(
        fields=(
            ("item", "item"),
            ("total", "total"),
        ),
    )

    class Meta:
        model = AggregatedPurchase
        fields = ("item",)

By using the above FilterSet in our view, we can now order by the aggregated field via URL parameters such as /purchases?sort=total.