Django: how to filter queryset by the combination of related objects fields?

Question

I have three related models that look like this:

class Library(models.Model):
    name = models.CharField(max_length=200, blank=False)

    class Meta:
        verbose_name = 'library'
        verbose_name_plural = 'libraries'


class Section(models.Model):
    name = models.CharField(max_length=200, blank=False)
    library = models.ForeignField(Library, related_name='sections', on_delete=models.CASCADE, null=False)

    class Meta:
        verbose_name = 'section'
        verbose_name_plural = 'sections'


class Book(models.Model):
    title = models.CharField(max_length=200, blank=False)
    section = models.ForeignField(Section, related_name='books', on_delete=models.CASCADE, null=False)
    is_available = models.BooleanField(default=True)

    class Meta:
        verbose_name = 'book'
        verbose_name_plural = 'books'

And say I need to filter all the libraries that have available books with title "The Lord of the Rings".

If I create a request like this

queryset = Library.objects.filter(sections__books__title="The Lord of the Rings", sections__books__is_available=True).distinct()

it will include libraries that have my book and it's not available, because the filter condition doesn't apply to the same book.

How do I specify that I need both filters to combine for related objects?

Answer 1

It will include libraries that have my book and it's not available, because the filter condition doesn't apply to the same book.

It does apply to the same book. If you specify this in the same .filter(…) ^[Django-doc] call, it will apply to the same book.

It will thus make a query that looks like:

SELECT DISTINCT library.*
FROM library
LEFT OUTER JOIN section ON section.library_id = library.id
LEFT OUTER JOIN book ON book.section_id = section.id
WHERE book.title = 'The Lord of the Rings'
  AND book.is_available

If you want to apply it to a different Book, then you specify it in two .filter(…) calls, so Library.objects.filter(sections__books__title='The Lord of the Rings').filter(sections__books__is_available=True).distinct(), and thus a total of four LEFT OUTER JOINs. But this is not what you want. If you want to add extra filtering, then you thus can do that in the same .filter(…) call, or in the same Q object ^[Django-doc].