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)
)
)
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')
)