Django Admin: two ListFilter Spanning multi-valued relationships

Question

I have a Blog model and an Entry model, following the example in django's documentation.

Entry has a ForeignKey to Blog: one Blog has several Entries.

I have two FieldListFilters for Blog: one for "Entry title", one for "Entry published year".

If in the Blog list admin page I filter for both entry__title='Lennon' and entry__published_year=2008, then I see all Blogs which have at least one entry with title "Lennon" and at least one entry from 2008. They do not have to be the same entry.

However, that's not what I want. What I want is to filter blogs which have entries that have both got the title "Lennon" and are from 2008.

So for example say I have this data:

Blog	Entry Title	Entry year
A	McCartney	2008
A	Lennon	2009
B	Lennon	2008

The admin list page for Blog currently filters in Blog A, because it has one entry from 2008 and one entry for "Lennon", as well as Blog B. I only want to see Blog B.

This is because django does this when it builds the queryset:

qs = qs.filter(title_filter)
qs = qs.filter(published_filter)

As per the docs, to get the desired result it would need to make just one filter call:

qs = qs.filter(title_filter & published_filter)

How can I achieve this behaviour with filtering in the admin?

Background:

Both filters are different concerning filtering on many-to-many relationships. See above link to the docs.

MyModel.filter(a=b).filter(c=d)

MyModel.filter(a=b, c=d)

Iranian

Can you show your existing admin models? – Pulling

Answer 1

So the fundamental problem as you point out is that django builds the queryset by doing a sequence of filters, and once a filter is "in" the queryset, it's not easy to alter it, because each filter builds up the queryset's Query object.

However, it's not impossible. This solution is generic and requires no knowledge of the models / fields you're acting on, but probably only works for SQL backends, uses non-public APIs (although in my experience these internal APIs in django are pretty stable), and it could get funky if you are using other custom FieldListFilter. The name was the best I could come up with:

from django.contrib.admin import (
    FieldListFilter,
    AllValuesFieldListFilter,
    DateFieldListFilter,
)

def first(iter_):
    for item in iter_:
        return item
    return None


class RelatedANDFieldListFilter(FieldListFilter):
    def queryset(self, request, queryset):
        # clone queryset to avoid mutating the one passed in
        queryset = queryset.all()

        qs = super().queryset(request, queryset)

        if len(qs.query.where.children) == 0:
            # no filters on this queryset yet, so just do the normal thing
            return qs

        new_lookup = qs.query.where.children[-1]
        new_lookup_table = first(
            table_name
            for table_name, aliases in queryset.query.table_map.items()
            if new_lookup.lhs.alias in aliases
        )
        if new_lookup_table is None:
            # this is the first filter on this table, so nothing to do.
            return qs

        # find the table being joined to for this filter
        main_table_lookup = first(
            lookup
            for lookup in queryset.query.where.children
            if lookup.lhs.alias == new_lookup_table
        )
        assert main_table_lookup is not None

        # Rebuild the lookup using the first joined table, instead of the new join to the same
        # table but with a different alias in the query.
        #
        # This results in queries like:
        #
        #   select * from table
        #   inner join other_table on (
        #       other_table.field1 == 'a' AND other_table.field2 == 'b'
        #   )
        #
        # instead of queries like:
        #
        #   select * from table
        #   inner join other_table other_table on other_table.field1 == 'a'
        #   inner join other_table T1 on T1.field2 == 'b'
        #
        # which is why this works.
        new_lookup_on_main_table_lhs = new_lookup.lhs.relabeled_clone(
            {new_lookup.lhs.alias: new_lookup_table}
        )
        new_lookup_on_main_table = type(new_lookup)(new_lookup_on_main_table_lhs, new_lookup.rhs)

        queryset.query.where.add(new_lookup_on_main_table, 'AND')
        return queryset

Now you can just make FieldListFilter subclasses and mix it in, I've just done the ones you wanted from the example:

class RelatedANDAllValuesFieldListFilter(RelatedANDFieldListFilter, AllValuesFieldListFilter):
    pass


class RelatedANDDateFieldListFilter(RelatedANDFieldListFilter, DateFieldListFilter):
    pass


@admin.register(Blog)
class BlogAdmin(admin.ModelAdmin):
    list_filter = (
        ("entry__pub_date", RelatedANDDateFieldListFilter),
        ("entry__title", RelatedANDAllValuesFieldListFilter),
    )

Cognate

@dapthdazz wow, that's a great solution. And thank yo for updating the answer and adding a table of example data. – Iranian

Answer 2

Solution

from django.contrib import admin
from django.contrib.admin.filters import AllValuesFieldListFilter, DateFieldListFilter

from .models import Blog, Entry


class EntryTitleFilter(AllValuesFieldListFilter):
    def expected_parameters(self):
        return []


class EntryPublishedFilter(DateFieldListFilter):
    def expected_parameters(self):
        # Combine all of the actual queries into a single 'filter' call
        return [
            "entry__pub_date__gte",
            "entry__pub_date__lt",
            "entry__pub_date__isnull",
            "entry__title",
            "entry__title__isnull",
        ]

@admin.register(Blog)
class BlogAdmin(admin.ModelAdmin):
    list_filter = (
        ("entry__pub_date", EntryPublishedFilter),
        ("entry__title", EntryTitleFilter),
    )

How this works

1. Under the hood, when a filter is initiated, django loops through the query parameters (from the request), and if they are in that filters' 'expected parameters', it stores them in a dict called self.used_parameters.
2. All built-in list filters apart from EmptyFieldListFilter inherit their queryset method from FieldListFilter. This method simply does queryset.filter(**self.used_parameters).
3. So by over-riding the expected_parameters method, we can control what happens when each filter is applied. In this case, we do all of the actual filtering in the entry-published filter.

Chiquia

Answer 3

Django apply list_filter sequentially in the list and checks every time queryset() method of list filter class. If return queryset is not None then django assign queryset = modified_queryset_by_the_filter.

we can use these points.

we can make two custom class filter for this,

first EntryTitleFilter class which queryset() method return None.

second MyDateTimeFilter class in which we access query params of both filter class then apply according to our requirement.

from django.contrib.admin.filters import DateFieldListFilter

class EntryTitleFilter(admin.SimpleListFilter):
    title = 'Entry Title'
    parameter_name = 'title'

    def lookups(self, request, model_admin):
        return [(item.title, item.title) for item in Entry.objects.all()]

    def queryset(self, request, queryset):
        # it returns None so queryset is not modified at this time.
        return None


class MyDateFilter(DateFieldListFilter):
    def __init__(self, *args, **kwargs):
        super(MyDateFilter, self).__init__(*args, **kwargs)

    def queryset(self, request, queryset):
        # access title query params
        title = request.GET.get('title')

        if len(self.used_parameters) and title:
            # if we have query params for both filter then
            start_date = self.used_parameters.get('entry__pub_date__gte')
            end_end = self.used_parameters.get('entry__pub_date__lt')
            blog_ids = Entry.objects.filter(
                        pub_date__gte=start_date,
                        pub_date__lt=end_end,
                        title__icontains=title
                    ).values('blog')
            queryset = queryset.filter(id__in=blog_ids)
        elif len(self.used_parameters):
            # if only apply date filter
            queryset = queryset.filter(**self.used_parameters)
        elif title:
            # if only apply title filter
            blog_ids = Entry.objects.filter(title__icontains=title).values('blog')
            queryset = queryset.filter(id__in=blog_ids)
        else:
            # otherwise
            pass
        return queryset



@admin.register(Blog)
class BlogAdmin(admin.ModelAdmin):
    list_filter = [EntryTitleFilter, ('entry__pub_date', MyDateFilter),]
    pass

Declassify

Answer 4

Let filters spanning multi-valued relationships be handled in ChangeList.get_queryset's qs.filter(**remaining_lookup_params) by returning an empty list [] in expected_parameters.

Unlike a few of the other answers, this avoids the dependency between filters.

Filter implementations and usage:

from django.contrib import admin

from .models import Blog, Entry


class EntryTitleFieldListFilter(admin.AllValuesFieldListFilter):
    def expected_parameters(self):
        return []  # Let filters spanning multi-valued relationships be handled in ChangeList.get_queryset: qs.filter(**remaining_lookup_params)


class EntryPublishedFieldListFilter(admin.AllValuesFieldListFilter):
    def __init__(self, field, request, params, model, model_admin, field_path):
        super().__init__(field, request, params, model, model_admin, field_path)
        field_path = 'entry__pub_date__year'
        self.lookup_kwarg = field_path
        self.lookup_kwarg_isnull = '%s__isnull' % field_path
        self.lookup_val = params.get(self.lookup_kwarg)
        self.lookup_val_isnull = params.get(self.lookup_kwarg_isnull)
        queryset = model_admin.get_queryset(request)
        self.lookup_choices = queryset.distinct().order_by(self.lookup_kwarg).values_list(self.lookup_kwarg, flat=True)

    def expected_parameters(self):
        return []  # Let filters spanning multi-valued relationships be handled in ChangeList.get_queryset: qs.filter(**remaining_lookup_params)


@admin.register(Blog)
class BlogAdmin(admin.ModelAdmin):
    list_filter = (
        ('entry__title', EntryTitleFieldListFilter),
        ('entry__pub_date', EntryPublishedFieldListFilter),
    )

---

Code reference for ChangeList.get_queryset's qs.filter(**remaining_lookup_params):

def get_queryset(self, request):
    # First, we collect all the declared list filters.
    (
        self.filter_specs,
        self.has_filters,
        remaining_lookup_params,
        filters_use_distinct,
        self.has_active_filters,
    ) = self.get_filters(request)
    # Then, we let every list filter modify the queryset to its liking.
    qs = self.root_queryset
    for filter_spec in self.filter_specs:
        new_qs = filter_spec.queryset(request, qs)
        if new_qs is not None:
            qs = new_qs

    try:
        # Finally, we apply the remaining lookup parameters from the query
        # string (i.e. those that haven't already been processed by the
        # filters).
        qs = qs.filter(**remaining_lookup_params)

Pulling

Answer 5

how about this when overriding the queryset method:

from django.db.models import Q

def queryset(self, request, queryset):
    title_filter = Q(entry_title='Lennon')
    published_filter = Q(entry_published_year=2008)
            
    return queryset.filter(title_filter | published_filter )

You any use any operators like &(bitwise AND), |(bitwise OR) like this.

Groping

Yes, this would work. Instead of creating two ListFilter, I create one ListFilter and there can execute both filter in one filter() call. – Iranian

Answer 6

I think this is what you want.

entries = Entry.objects.filter(title='Lennon', published_year=2008)
blogs = Blog.objects.filter(entry__in=entries)

Cradlesong

Yes, this work if you can use the ORM. I am using the django-admin and two ListFilter. The ListFilter can only implement a queryset() method. Sorry, your answer is great, but does not fit to the question. – Iranian