Weird Behaviour when filtering Model objects by a related model

[YasirKusay]

Lets say we have an app called: filter with 2 models: Author and Book where an Author can have many Book objects.

# models.py
from django.db import models

# Create your models here.
class Author(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name
    
class Book(models.Model):
    title = models.CharField(max_length=200)
    genre = models.CharField(max_length=100)
    author = models.ForeignKey(Author, related_name='books', on_delete=models.CASCADE)

    def __str__(self):
        return self.title

We also have defined the filter here, where we can filter the Author based on title and genre of their books:

# filters.py
import django_filters
from filter.models import Author, Book

class FilterAuthorByBook(django_filters.FilterSet):
    class Meta:
        model = Author
        fields = {
            'books__title': ['icontains'],
            'books__genre': ['icontains']
        }

Now lets create an example

python3 manage.py shell
>>> from filter.models import Author, Book
>>> from filter.filters import FilterAuthorByBook
>>> shakespeare = Author(name="William Shakespeare")
>>> shakespeare.save()
>>> othello = Book(title="Othello", genre="Tragedy", author=shakespeare)
>>> othello.save()
>>> henry4 = Book(title="Henry 4", genre="History", author=shakespeare)
>>> henry4.save()

We would like to filter authors by the book title "othello" and the "history" book genre. I would expect that nothing would get returned, but it appears that it is filtering the author based on if their book field matches at least one filter we applied, whereas I want the author's books to match all applied fields, for an author to be returned.

>>> queryset = FilterAuthorByBook({"books__title__icontains": "othello", "books__genre__icontains": "history"})
>>> queryset.qs.all()
<bound method QuerySet.all of <QuerySet [<Author: William Shakespeare>]>>

Is this the intended behaviour? How can I change it such that the author's books must match all applied fields, for an author to be returned.

[YasirKusay]

I have an update to this, take a look at the example below:

>>> queryset = FilterAuthorByBook({"books__title__icontains": "othello", "books__genre__icontains": "comedy"})
>>> queryset.qs
<QuerySet []>

The above returns nothing and it appears that your algorithm searches through an Authors books and returns the Author if at least one book contains "othello" in the title AND at least one book is a "comedy". I.e. all the filters we apply must contained by the fields of our books in a non-intersecting manner. Since none of the Books we have for this author is a comedy, the author will not be returned. This makes sense but I would still like to have my original method. Is there a way to do this?

[carltongibson]

This is standard Django behaviour. See the Spanning multi-valued relationships docs.

If you need to filter in a single step, define a filter that takes both fields from the query data and handles those together. There's an example of that here: https://gitlab.com/-/snippets/2237049

[YasirKusay]

Thank you for that. Taking all of this into account, I have added this filtering method to the FilterAuthorByBook class:

    def filter_queryset(self, queryset):
        filter_conditions = Q()
        filter_data = self.data

        for field, value in filter_data.items():
            if field.startswith('books__') and value:
                filter_conditions &= Q(**{field: value})

        queryset = queryset.filter(filter_conditions).distinct()

        return queryset

It appears to filter items now as I intended.