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