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.

Answer 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=[user1, user2, user3]
).count()

or if you have ids:

Post.objects.filter(
    post_user_id__in=[user_id1, user_id2, user_id3]
).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))
)