django annotate models with an aggregate value based on query

Question

Let's say I have the following model structure:

Parent():

Child():
parent = ForeignKey(Parent)

GrandChild():
child = ForeignKey(Child)
state = BooleanField()
num = FloatField()

I'm trying to from the Parent ViewSet, recover the following:

1. The number of children.
2. The SUM of the 'num' fields when 'state' is True.

I can do the following:

queryset = Parent.objects\
    .annotate(child_count=Count('child'))\
    .annotate(sum_total=Sum('child__grandchild__num'))

This gives me (1) but instead of (2) it gives me the SUM for ALL grandchildren. How can I filter the grandchildren appropriately while ensuring I have all of the Parent objects still in the QuerySet?

Answer 1

Which version of django are you using? You can use subquery as well if version is supported.

from django.db.models import OuterRef, Subquery

Parent.objects
.annotate(child_count=Count('child'))
.annotate(
    grandchild_count_for_state_true=Subquery(
        GrandChild.objects.filter(
            state=True,
            child=OuterRef('pk')
        ).values('parent')
        .annotate(cnt=Sum('child__grandchild__num'))
        .values('cnt'),
        num=models.IntegerField()
    )
)

You can optimise this through aggregation query.