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

score 0 · Answer 1 · answered Oct 05 '22 at 01:20

0

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

answered Oct 05 '22 at 01:20

A_K

731
3
15
40

Thanks, but I'm trying to get it all in one query. – user15656060 Oct 05 '22 at 01:40

score 0 · Accepted Answer · answered Oct 05 '22 at 01:53

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

Counting the children matching grandchildren conditions in a single Django query

2 Answers2