Django models: Aggregate Sum over multiple foreign key field values

Question

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.

the best way is to do it in the database. write a simple function that does it — Mox
– Mox, Commented Mar 5, 2016 at 19:19

Aswin Murugesh · Accepted Answer · 2016-03-05 19:28:54Z

3

In django, you can try:

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

edited Mar 5, 2016 at 19:28

answered Mar 5, 2016 at 19:22

Aswin Murugesh

11.1k11 gold badges46 silver badges73 bronze badges

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

6 Comments

Jones Over a year ago

I tried this. While the only problem was that this will not generate unique touples of user pks this is still usefull. All that is necesscary would be to sum up the equivalent touples. I only thought there may be a better way. eg: [{'target': 4, 'sum_pts': 1, 'author': 1}, {'target': 5, 'sum_pts': 4, 'author': 1}, {'target': 1, 'sum_pts': -3, 'author': 4}, {'target': 1, 'sum_pts': 1, 'author': 5}]

Aswin Murugesh Over a year ago

@Jones What do you mean by not generate unique tuples of user pks?

Jones Over a year ago

there will be the {1,2} and {2,1} touples

Aswin Murugesh Over a year ago

@Jones Yes even normal db queries will not do this, because though they reference the same model, they are 2 different fields. So, I think iteration is the only option if you want exactly what you ask for

Aswin Murugesh Over a year ago

@Jones No problem :) I can even help you with an iterative solution if that is what you want

|

Collectives™ on Stack Overflow

Django models: Aggregate Sum over multiple foreign key field values

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