Django: Annotate based on an annotation

Question

Let's say I'm using Django to manage a database about athletes:

class Player(models.Model):
    name = models.CharField()
    weight = models.DecimalField()
    team = models.ForeignKey('Team')

class Team(models.Model):
    name = models.CharField()
    sport = models.ForeignKey('Sport')

class Sport(models.Model):
    name = models.CharField()

Let's say I wanted to compute the average weight of the players on each team. I think I'd do:

Team.objects.annotate(avg_weight=Avg(player__weight))

But now say that I want to compute the variance of team weights within each sport. Is there a way to do that using the Django ORM? How about using the extra() method on a QuerySet? Any advice is much appreciated.

Fumed

Could you give us an example of the sort of calculation you'd want to do? I'm assuming you mean avg_weight - specific_player.weight. – Jokjakarta

You can't reference a field created by an annotate call in the extra call. However, you could try something like this one: https://mcmap.net/q/787102/-using-extra-on-fields-created-by-annotate-in-django – Jokjakarta

The specific example from the original post (variance) is the one I had in mind. But just for variety, I might also want to know, for each sport, what is the Maximum average weight of each team in that sport. Does that make sense? – Fumed

Django also has an aggregation function for variance just like Avg – Bodice

Answer 1

you can use query like this :

class SumSubquery(Subquery):
    template = "(SELECT SUM(`%(field)s`) From (%(subquery)s _sum))"
    output_field = models.Floatfield()
    def as_sql(self, compiler, connection, template=None, **extra_context):
        connection.ops.check_expression_support(self)
        template_params = {**self.extra, **extra_context}
        template_params['subquery'], sql_params = self.queryset.query.get_compiler(connection=connection).as_sql()
        template_params["field"] = list(self.queryset.query.annontation_select_mask)[0]
        sql = template % template_params
        return sql, sql_params



Team.objects.all().values("sport__name").annotate(variance=SumSubquery(Player.objects.filter(team__sport_id=OuterRef("sport_id")).annotate(sum_pow=ExpressionWrapper((Avg("team__players__weight") - F("weight"))**2,output_field=models.Floatfield())).values("sum_pow"))/(Count("players", output_field=models.FloatField())-1))

and add related name to model like this:

class Player(models.Model):
    name = models.CharField()
    weight = models.DecimalField()
    team = models.ForeignKey('Team', related_name="players")

Tungsten

Answer 2

I'm going to assume (perhaps incorrectly) that you mean by 'variance' the difference between maximum and minimum weights. If so, you can generate more than one aggregate with a single query, like so:

from django.db.models import Avg, Max, Min

Team.objects.aggregate(Avg('player__weight'), Max('player__weight'), Min('player__weight'))

This is taken from the django docs on generating aggregation over a queryset.

Dewees

Answer 3

you could try using a @property inside of Team

something like:

class Team(models.Mode):
    name = models.CharField()
    sport = models.ForeignKey('Sport')
    @property
    def avg_weight(self):
        list_of_weights = []
        for player in Player.objects.filter(Team = self):
            list_of_weights.append(player.weight)
            return sum(list_of_weights) / len(list_of_weights)

Difficulty