Django Annotate and Aggregate

Question

I'd like to Sum the post_value of all of the Posts for each post_user to eventually use in a chart. I'm struggling with how to formulate the query?

So far, I've got to:

user_totals = User.objects.annotate(post_value_total=Sum('post'))

models.py

class User(AbstractUser):
    pass

class Post(models.Model):
    post_user = models.ForeignKey(User, on_delete=models.CASCADE)
    post_cat = models.ForeignKey(Category, on_delete=models.CASCADE)
    post_action = models.ForeignKey(Action, on_delete=models.CASCADE)
    post_quantity = models.PositiveIntegerField(blank=True, null=True)
    post_value = models.PositiveIntegerField(default='0')
    post_timestamp = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return f"{self.post_user}'s post at {self.post_timestamp}"

Thanks.

So you want the total number of `Post`s? Since each `Post` has a non-NULL field that refers to a `post_user`, the total number of `Post`s for all users, is the number of `Post`s in total. — Willem Van Onsem, May 20 '21 at 10:57

Willem Van Onsem · Accepted Answer · 2021-05-20T15:55:33.217

1

I'd like to Sum the post_value of all of the Posts for each post_user to eventually use in a chart.

Since each Post has a non-nullable post_user ForeignKey, it means that each Post belongs to exactly one user.

We thus can sum up the number of post_values of all Users with:

Post.objects.all().count()

If you only want to sum these up for a subset of the users, you can work with:

Post.objects.filter(
    post_user__in=[user₁, user₂, user₃]
).count()

or if you have ids:

Post.objects.filter(
    post_user_id__in=[user_id₁, user_id₂, user_id₃]
).count()

Or if you want to sum up the post_values, you can work with:

from django.db.models import Sum

total_post_value = Post.objects.aggregate(
    total=Sum('post_value')
) or 0

The or 0 is necessary if the collection can be empty, since the sum of no records is NULL/None, not 0.

Or if you want to do this per User, we can work with:

user_totals = User.objects.annotate(
    post_value_total=Sum('post__post_value')
)

The User objects that arise from this will have an extra attribute post_value_total that sums up the values of the related Posts. These can be None if a user has no related Posts. In that case we can work Coalesce [Django-doc]:

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

user_totals = User.objects.annotate(
    post_value_total=Coalesce(Sum('post__post_value'), Value(0))
)

edited May 20 '21 at 15:55

answered May 20 '21 at 11:00

Willem Van Onsem

443,496
30
428
555

Thanks Willem, but I am trying to Sum the post_value of all of the Posts that each User has recorded, not the number of Posts for each User? – Ewan Willsmer May 20 '21 at 11:19
@EwanWillsmer: by using count, we *aggregate*, so we will count the total number of posts submitted by *all* users (code fragment 1), or if we want to do that for a specific group for users (fragments 2 and 3). These will thus all return a *scalar* integer. – Willem Van Onsem May 20 '21 at 11:20
post_value is a field in the Post model. I am not interested in the number of Posts, I am interested in the total post_value for all of the Posts related to each User. – Ewan Willsmer May 20 '21 at 12:26
@EwanWillsmer: then you thus simply want to sum up the `post_value`s for each `Post`? Since each `Post` relates to exactly *one* it does not matter to first annotate. – Willem Van Onsem May 20 '21 at 15:07
I really appreciate your help, but I would like to get a total_post_value for each User. The total_post_value would be the sum of the post_value from each of their Posts. – Ewan Willsmer May 20 '21 at 15:51
@EwanWillsmer: see edit to calculate this *per* `User`. – Willem Van Onsem May 20 '21 at 15:55

Django Annotate and Aggregate

1 Answers1