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 Posts? Since each Post has a non-NULL field that refers to a post_user, the total number of Posts for all users, is the number of Posts in total. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented May 20, 2021 at 10:57

willeM_ Van Onsem · Accepted Answer · 2021-05-20 15:55:33Z

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, 2021 at 15:55

answered May 20, 2021 at 11:00

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

6 Comments

Ewan Willsmer Over a year ago

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?

willeM_ Van Onsem Over a year ago

@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.

Ewan Willsmer Over a year ago

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.

willeM_ Van Onsem Over a year ago

@EwanWillsmer: then you thus simply want to sum up the post_values for each Post? Since each Post relates to exactly one it does not matter to first annotate.

Ewan Willsmer Over a year ago

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.

|

Collectives™ on Stack Overflow

Django Annotate and Aggregate

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related