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?

Statutory

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.

Cartogram

A subquery may be the route I need to follow but your answer is not correct. 1. I am looking for a Sum, not a Count. 2. I do not have a model named 'Event'. 3. Field 'num' is a FloatField, not an IntegerField. – Statutory

Answer 2

Try using filter before the annotate

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

Exigent

That would work, but I need to ensure I have all Parent objects. Sorry I'll edit the initial question. – Statutory

@Statutory You will get the parent object queryset from this. – Exigent

What if the particular Parent has no GrandChild? – Statutory

This will select only those objects where grandchild state is True so if there is no grandchild then it won't be included in the queryset @Statutory – Exigent

you do know that annotate is used for grouping the data. It will output a dict like object where each object would have at least one pk param from parent object. You can use that param to get the Parent object from database by iterating over whole output – Exigent

Answer 3

You can do the following:

qs = Parents.objects.all()
child_count = Count('children')
num_sum = Sum('children__grandchildren__num', filter=Q(children__grandchildren__state=True))
qs = qs.annotate(child_count=child_count).annotate(num_sum=num_sum)

chidlren and grandchildren are the related names that you can define in your models

Loram