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)

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),
    )