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?
This query should be sufficient