Optimize Django ORM query to get object if a specific related object does not exist

I have the following table structures:

class Library:
    id = models.CharField(...)
    bookcase = models.ForeignKey(
        Bookcase,
        related_name="libraries"
    )
    location = models.ChoiceField(...)
    # Other attributes...

class Bookcase:
    # some attributes
    type = models.ChoiceField(..)

class Book:
    bookcase = models.ForeignKey(
        Bookcase,
        related_name="books"
    )
    title=models.CharField(...)
    status=models.ChoiceField(...) # borrowed | missing | available
       

Say if I want to get all Library objects that does not have a book with title "Foo" that is NOT missing, how can I optimize this query? I have the following:

libraries = Library.objects.select_related('bookcase').filter(location='NY', bookcase__type='wooden')

libraries_without_book = []
for library in libraries:
    has_non_missing_book = Book.objects.filter(
        bookcase=library.bookcase,
        title="Foo",
    ).exclude(status='missing').exists()
    
    if not has_non_missing_book:
        libraries_without_book.append(library.id)

Unfortunately, this performs an extra query for every Library object that matches the initial filtering condition. Is there a more optimized method I can use here that makes use of prefetch_related in some way?

Book.objects.filter(~Q(status='missing'),bookcase=library.bookcase,title='Foo')

This query should be sufficient

Back to Top