CODEWITHSUNDEEP
pythondjango
RaideR
RaideR

Reputation: 937

Django: Annotating Sum() of two columns in different tables

Assuming I have the following models - How can I annotate the total posting and total story reach of each influencer in my queryset?

class Influencer(models.Model):
    name = models.CharField(max_length=100)

class Posting(models.Model):
    influencer = models.ForeignKey(Influencer, on_delete=models.CASCADE)
    reach = models.IntegerField()

class Story(models.Model):
    influencer = models.ForeignKey(Influencer, on_delete=models.CASCADE)
    reach = models.IntegerField()

I have tried this:

queryset = Influencer.objects.all()
queryset = queryset.annotate(posting_reach=Sum("posting__reach"))
queryset = queryset.annotate(story_reach=Sum("story__reach"))

However, the values are not calculated correctly using this approach (I assume because of the LEFT OUTER JOIN which is made by Sum()). How would I do this in Django?

Upvotes: 1

Views: 1717

Answers (2)

Risadinha
Risadinha

Reputation: 16666

For anyone interested in a subquery solution:

from django.db.models import (OuterRef, Subquery, Sum, IntegerField)

subs = {
    'posting_reach': Posting.objects.filter(influencer=OuterRef('pk')),
    'story_reach': Story.objects.filter(influencer=OuterRef('pk')),
}
qs = Influencer.objects.annotate(
    **{key: Subquery(
            sub_q.annotate(sum=Sum('reach')).values('sum'),
            output_field=IntegerField()
        ) for key, sub_q in subs
    }
)

Upvotes: 0

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476554

This will indeed not work, because your query will make two LEFT OUTER JOINs:

SELECT influencer.*, SUM(posting.reach), SUM(story.reach)
FROM influencer
LEFT OUTER JOIN posting ON posting.influencer_id = influencer.id
LEFT OUTER JOIN story ON story.influencer_id = influencer.id

the two LEFT OUTER JOINs will thus act as a multiplier of each other, and thus the sum of posting.reach will be multiplied by the number of related storys, and vice versa.

A trick could be to divide it by the number of items of the other relation, so:

from django.db.models import Count, Sum, Value
from django.db.models.functions import Coalesce

queryset = Influencer.objects.annotate(
    posting_reach=Sum('posting__reach') / Coalesce(Count('story'), Value(1)),
    story_reach=Sum('story__reach') / Coalesce(Count('posting'), Value(1))
)

Upvotes: 3

Related Questions