CODEWITHSUNDEEP
djangodatabasesumaggregateannotate
Jones
Jones

Reputation: 239

Django models: Aggregate Sum over multiple foreign key field values

I have the following Django database Model:

class Entry(models.Model):
     author = models.ForeignKey(User);
     target = models.ForeignKey(User);
     points = models.IntegerField(default=0);

Users can give another user points. The goal is to calculate the Sum of points for each Tuple of Users. The obvious way is to use iteration but this would result in many unnecessary queries. While this is a rather simple SQL query I cant get it to work with djangos API. I played around with annonate, aggregate and Q filters but could not achieve the desired result.

Preferably the result would be something like:

[{'user_one_pk': 1, 'user_two_pk': 2, 'sum__points': 6},
 {'user_one_pk': 1, 'user_two_pk': 3, 'sum__points': -3},
 {'user_one_pk': 2, 'user_two_pk': 3, 'sum__points': 9}]

EDIT: edited result for clarification of unique tuples.

Upvotes: 2

Views: 2635

Answers (1)

Aswin Murugesh
Aswin Murugesh

Reputation: 11070

In django, you can try:

from django.db.models import Sum
Entry.objects.all().values('author','target').annotate('sum__points'=Sum('points'))

Upvotes: 3

Related Questions