Django Sum in Annotate

Question

Good afternoon,

I am really struggling with getting a sum using Annotate in DJango.

I am using User object and the following models:

class Depts(models.Model):
    dept_name = models.CharField(max_length=55)
    dept_description = models.CharField(max_length=255)
    isBranch = models.BooleanField(default=False)

    def __str__(self):
        return "{}".format(self.dept_name)


class UserProfile(models.Model):
    user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='profile')
    title = models.CharField(max_length=75)
    dept = models.ForeignKey(Depts, on_delete=models.CASCADE, related_name="dept", null=True)

class ActivityLog(models.Model):
    activity_datetime = models.DateTimeField(default=timezone.now)
    user = models.ForeignKey(User, on_delete=models.CASCADE, null=True, related_name='activity_user')
    activity_category = models.ForeignKey(ActivityCategory, on_delete=models.CASCADE, null=True, related_name='activity_cat')
    activity_description = models.CharField(max_length=100, default="Misc Activity")

class ActivityCategory(models.Model):
    activity_name = models.CharField(max_length=40)
    activity_description = models.CharField(max_length=150)
    pts = models.IntegerField()

    def __str__(self):
        return '%s' % (self.activity_name)

What I need to do is get a group of departments with aggregating the sum of the pts earned by all the users activitylogs.

So a user is part of department, they do activities, each activity is of a type activity_category and has associated points. How can I query using the ORM to get a sum of points for everyone in each department?

Thank you, I cannot seem to wrap my mind around it.

willeM_ Van Onsem · Accepted Answer · 2020-10-15 20:49:12Z

1

You annotate the departments with the sum:

from django.db.models import Sum

Depts.objects.annotate(
    total_pts=Sum('dept__user__activity_user__activity_category__pts')
)

Note: The related_name=… parameter [Django-doc] is the name of the relation in reverse, so from the Depts model to the UserProfile model in this case. Therefore it (often) makes not much sense to name it the same as the forward relation. You thus might want to consider renaming the ~~dept~~ relation to userprofiles.

After setting the related_name='userprofiles', the query is:

from django.db.models import Sum

Depts.objects.annotate(
    total_pts=Sum('userprofiles__user__activity_user__activity_category__pts')
)

answered Oct 15, 2020 at 20:49

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

1 Comment

Bring Coffee Bring Beer Over a year ago

You are awesome. And tremendously helpful... Thank you! Just seeing it the right way helps it make sense.

Collectives™ on Stack Overflow

Django Sum in Annotate

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related