Counting the children matching grandchildren conditions in a single Django query

Question

Given the following models:

class Flight:

class Checklist:
    flight = ForeignKey(Flight)

class Item:
    checklist = ForeignKey(Checklist)
    completed = BooleanField()

I need to get the number of completed checklists for each flight. A checklist is considered completed when all of its items are completed.

I know for checklists I can do

Checklist.objects.annotate(
    is_complete=~Exists(Item.objects.filter(
        completed=False, 
        checklist_id=OuterRef('pk'),
    ))
)

but I need something for flights, ideally a single query. Something like

Flight.objects.annotate(
    completed_checklists=Count(
        Checklist.objects.annotate(<is complete annotation here>).filter(is_complete=True)
    )
)

Answer 1

Not sure if it's the most efficient way but you can count the total number of checklists and subtract the number of checklists that have at least one incomplete item

from django.db.models import Count, Q, F

Flight.objects.annotate(
    checklist_count=Count('checklist', distinct=True),
    incomplete_checklists=Count('checklist', filter=Q(checklist__item__completed=False), distinct=True)
).annotate(
    completed_checklists=F('checklist_count') - F('incomplete_checklists')
)