Counting the children matching grandchildren conditions in a single Django query-CodePudding

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

CodePudding user response：

You can add a function in the models something like the below:

    def get_total(self):
        #start with a zero total
        total = 0
        #check for all the checklist in item
        for b in self.checklist.all():
        #if there is a checklist that is completed add 1
        if self.completed:
            total  = 1
        return total

You can then use get_total

CodePudding user response：

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