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:

The number of children.
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?

Manel Clos · Accepted Answer · 2020-06-16 19:13:32Z

5

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.

edited Jun 16, 2020 at 19:13

Manel Clos

1,8452 gold badges21 silver badges16 bronze badges

answered Sep 15, 2017 at 18:16

Pati Ram Yadav

9451 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

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.

revliscano · Accepted Answer · 2021-10-05 19:44:43Z

1

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

edited Oct 5, 2021 at 19:44

revliscano

2,2722 gold badges14 silver badges23 bronze badges

answered Sep 15, 2017 at 18:01

Arpit Solanki

10k4 gold badges45 silver badges57 bronze badges

5 Comments

Eric Over a year ago

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

Arpit Solanki Over a year ago

@Eric You will get the parent object queryset from this.

Eric Over a year ago

What if the particular Parent has no GrandChild?

Arpit Solanki Over a year ago

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 @Eric

Arpit Solanki Over a year ago

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

ZaherSarieddine · Accepted Answer · 2021-08-03 18:08:37Z

0

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

edited Aug 3, 2021 at 18:08

answered Aug 3, 2021 at 17:48

ZaherSarieddine

1264 bronze badges

Collectives™ on Stack Overflow

django annotate models with an aggregate value based on query

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related