CODEWITHSUNDEEP
django
ataraxis
ataraxis

Reputation: 1562

Aggregation in Django

I have the following Model in Django:

class A(models.Model):
    nr = models.IntegerField()

class B(models.Model):
    points = models.IntegerField()

class C(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    b = models.ForeignKey(B, on_delete=models.CASCADE)

So for every A there are many entries in C, and for every B there are also many entries in C. But for every entry in C there is exactly one entry in A and one in B.

I would like to sum up the B.points for a given A, but I have to go over C.

How can I do that in Django? I would know how to do that in SQL if that helps?

Upvotes: 1

Views: 223

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476554

You can .annotate(..) [Django-doc] the As, like:

from django.db.models import Sum

A.objects.annotate(
    total_points=Sum('c__b__points')
)

If you for example always want to annotate your A objects, you can define a manager for that:

class WithPointsManager(models.Manager):

    def get_queryset(self):
        return super().get_queryset().annotate(
            total_points=Sum('c__b__points')
        )

and then define this manager on the A class, like:

class A(models.Model):
    nr = models.IntegerField()

    objects = WithPointsManager()

So now if you perform an A.objects.all(), the As will have a total_points attribute. Note that of course this will have a certain cost at the database side.

Then all A objects that arise from this QuerySet, will contain an extra .total_points attribute that contains the sum of all Bs that are related to a C that is related to that A.

Or for a given A object, you can .aggregate(..) [Django-doc], like:

from django.db.models import Sum

some_a.c_set.aggregate(
    total_points=Sum('b__points')
)['total_points']

This will return the sum of the points of related B objects of the set of Cs that are related to some_a.

Upvotes: 4

Related Questions