django annotate models with an aggregate value based on query

user_659841 0
Viewed 6184

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?

django-aggregation django-annotate django-queryset django-models django
3 Answers

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

user_29989800
Related