from django.db.models import Count

# Using Subquery
authors_with_book_count = Author.objects.annotate(book_count=Subquery(Book.objects.filter(author=OuterRef('pk')).values('author').annotate(count=Count('id')).values('count')))

# Equivalent Non-Subquery Approach
authors = Author.objects.all()
authors_with_book_count = []
for author in authors:
    book_count = Book.objects.filter(author=author).count()
    authors_with_book_count.append({'author': author, 'book_count': book_count})
Copy