Django - aggregate fields value from joined model

Question

my goal here seems to be simple: display the Sum (aggregation) of a foreign model particular field. The difficulty consist in the current set-up, kindly take a look and let me know if this need to be changed or I can achieve the goal with current model:

class Route(models.Model):
name = models.CharField(max_length=50)
route_length = models.IntegerField()

class Race(models.Model):
race_cod = models.CharField(max_length=6, unique=True)
route_id = models.ForeignKey(Route, on_delete=models.CASCADE, related_name='b_route')

class Results(models.Model):
race_id = models.ForeignKey(Race, on_delete=models.CASCADE, related_name='r_race')
runner_id = models.ForeignKey(Runner, on_delete=models.CASCADE, related_name='r_runner')

Now, I am trying to have like a year summary:

Runner X have raced in 12 races with a total distance of 134 km.

While I was able to count the number of races like this (views.py)

runner = Runner.objects.get(pk=pk)
number_races = Results.objects.filter(runner_id=runner).count()

For computing the distance I have tried:

distance = Results.objects.filter(runner_id=runner).annotate(total_km=Sum(race_id.route_id.route_length))

This code error out stating that on views.py - distance line

Exception Type: NameError Exception Value: name 'race_id' is not defined

I am sure I did not u/stood exactly how this works. Anybody kind enough to clarify this issue?

Thank you

score 1 · Answer 1 · answered Feb 29 '20 at 23:00

My workaround is the following :

    tmp_race_id = Results.objects.filter(runner_id=runner).values('race_id')
    tmp_route_id = Race.objects.filter(pk__in=tmp_race_id).values('route_id')
    distance = Route.objects.filter(pk__in=tmp_route_id).aggregate(Sum("route_length "))['route_length __sum'] or 0.00

Thank you Jorge Lopez for the hint.

score 0 · Answer 2 · answered Feb 28 '20 at 20:21

0

you don´t need a Results Model, you can calculate using the data in the models, can you share your Runner Model? that model needs to have a foreign key to a Race. if that is so, you can go from Route's -> Race -> Runner in your query, and you can use the query for the count, so you will have a variable where you stored the count and a variable where you stored the distance. To do a Sum in your query do not use annotate, use aggregate, something like this:

.aggregate(total=Coalesce(Sum('route_lenght'), 0))['total']

answered Feb 28 '20 at 20:21

Jorge López

489
3
17

Hey, Runner class have just id, name of the runner and some personal data. I cannot put a FK to Race there since the logic is like this: add a runner -> add a route -> add a Race -> put results in Race. – Tizu Feb 28 '20 at 21:58
sorry that I couldn't respond sooner, I was off my computer during the weekend, I'm glad you found a workaround. – Jorge López Mar 02 '20 at 15:47

score 0 · Answer 3 · answered Mar 04 '20 at 12:52

0

do like this

from django.db.models import Sum, Count

u = Runner.objects.annotate(
    tot_result=Count('r_runner'), 
    tot_km=Sum('r_runner__race_id__route_id__route_length')
)

for i in u: 
    print('Total_race {} -- Total_Km {}'.format(i.tot_result, i.tot_km))

answered Mar 04 '20 at 12:52

Nikunj

305
1
7

Thanks for your first answer here. Maybe you should think about updating your answer with some helpful explanation to your code. – T. Christiansen Mar 04 '20 at 13:16

Django - aggregate fields value from joined model

3 Answers3