6

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?

Arpit Solanki
  • 9,567
  • 3
  • 41
  • 57
Eric
  • 601
  • 7
  • 22

3 Answers3

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.

Manel Clos
  • 1,785
  • 2
  • 19
  • 15
Pati Ram Yadav
  • 815
  • 1
  • 8
  • 22
  • 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. – Eric Sep 15 '17 at 18:59
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'))
revliscano
  • 2,227
  • 2
  • 12
  • 21
Arpit Solanki
  • 9,567
  • 3
  • 41
  • 57
  • That would work, but I need to ensure I have all Parent objects. Sorry I'll edit the initial question. – Eric Sep 15 '17 at 18:03
  • @Eric You will get the parent object queryset from this. – Arpit Solanki Sep 15 '17 at 18:04
  • What if the particular Parent has no GrandChild? – Eric Sep 15 '17 at 18:04
  • 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 Sep 15 '17 at 18:05
  • 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 – Arpit Solanki Sep 15 '17 at 18:09
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

ZaherSarieddine
  • 116
  • 4