Django annotation resulting in cartesian product

Question

I have a model where a particular class will have multiple child classes. Sample design:

class ParentModel(models.Model):
   ParentName = CharField(max_length=50)

class ChildModelA(models.Model):
   FK1 = ForeignKey(ParentModel, on_delete=models.CASCADE)
   ChildAValue = IntegerField(default=0)

class ChildModelB(models.Model):
   FK2 = ForeignKey(ParentModel, on_delete=models.CASCADE)
   ChildBValue = IntegerField(default=0)

What I'm trying to do is sum up, per 'ParentName', the sum of all ChildAValue and ChildBValue. The issue I'm running in to is when I do both in the same query, I get a cartesian product between ChildA & ChildB. Sample query:

ParentModel.objects.all().values('ParentName').annotate(
   ChildASum = Sum('childamodel__ChildAValue),
   ChildBSum = Sum('childbmodel__ChildBValue),
)

The resulting query set gives me the summation of ChildBValue multiplied by the total number of ChildA objects in the FK... meaning 6 ChildA objects pointing to that particular Parent will multiply my ChildB value by 6. And vice-versa for the ChildASum.

Are there any ways to aggregate multiple child models of the same parent without a multi-join?

Thanks!

score 1 · Answer 1 · answered Jan 12 '20 at 00:09

Yes, you probably need to work with subqueries here:

from django.db.models import OuterRef, Subquery, Sum

ParentModel.objects.values('ParentName').annotate(
    ChildASum=Subquery(
        ChildModelA.objects.filter(
           FK1=OuterRef('pk')
        ).annotate(
            total=Sum('ChildAValue')
        ).values('total').order_by('FK1')
   ),
   ChildBSum=Subquery(
        ChildModelB.objects.filter(
           FK2=OuterRef('pk')
        ).annotate(
            total=Sum('ChildAValue')
        ).values('total').order_by('FK1')
   )
)

Django annotation resulting in cartesian product

1 Answers1