CODEWITHSUNDEEP
djangodjango-modelsdjango-rest-frameworkdjango-orm
Bring Coffee Bring Beer
Bring Coffee Bring Beer

Reputation: 1121

Django Sum in Annotate

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.

Upvotes: 0

Views: 65

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476659

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

Upvotes: 1

Related Questions