Django compute score of matches (workaround to annotate a query after a union)

Question

I need to compute the ranking of athletes during boxing tournaments. In my models, I track the result of each match and the points to be attributed for each outcome to each athlete.

class Member(models.Model):
    surname = models.CharField(max_length=200)
    last_name = models.CharField(max_length=200)

class Tournament(models.Model):
    name = models.CharField(max_length=200)

class TrophyRule(models.Model):
    win = models.IntegerField()
    loose = models.IntegerField()
    draw = models.IntegerField()

class Ring(models.Model):
    code = models.CharFieldmax_length=1)
    tournament = models.ForeignKey(Tournament, on_delete=models.CASCADE)

class Match(models.Model):
    ring = models.ForeignKey(Ring, null=True, on_delete=models.SET_NULL)
    winner = models.CharField(max_length=20, null=True, blank=True)
    trophy_rule = models.ForeignKey(TrophyRule, on_delete=models.SET_NULL, null=True)
    red_member = models.ForeignKey(Member, related_name='reds', on_delete=models.SET_NULL, null=True)
    red_count_ranking = models.BooleanField(default=True)
    blue_member = models.ForeignKey(Member, related_name='blues', on_delete=models.SET_NULL, null=True)
    blue_count_ranking = models.BooleanField(default=True)

Based on this model, I need to sum the points acquired when the athlete was in the red corner with the points acquired when the athlete was in the blue corner. The result should be a queryset with all members and their total number of points.

In order to achieve this, I started with the computation of the points acquired by the athletes in the red corner:

from apps.members.models import Member
from django.db.models import Case, Sum, When, Q

red = Member.objects.filter(reds__ring__tournament_id=11402).annotate(
    points=Case(
        When(Q(reds__winner='red') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__win'),
        When(Q(reds__winner='draw') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__draw'),
        When(Q(reds__winner='blue') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__loose'),
    ),
)

I also did the same for the points acquired by the athletes in the blue corner:

blue = Member.objects.filter(blues__ring__tournament_id=11402).annotate(
    points=Case(
        When(Q(blues__winner='blue') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__win'),
        When(Q(blues__winner='draw') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__draw'),
        When(Q(blues__winner='red') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__loose'),
    ),
)

Now, I need to combine the two queries and sum the points for each athlete. This is the part where I am stuck at the moment.

I tried to use union() which translates to a SQL UNION:

red.union(blue)

If I have 4 matches, with union() I get a queryset with 8 members (4 red and 4 blue), which is exactly what I am looking for. Unfortunately, when I try to compute the final number of points (points when the athlete was red + points when the athlete was blue), I trigger the error: Calling QuerySet.annotate() after union() is not supported (as per documentation).

red.union(blue).annotate(Sum('points'))

Is there an other way to achieve this with the Django ORM? I prefer not to revert to raw SQL if not necessary.

score 0 · Answer 1 · answered Aug 23 '20 at 17:35

This might be possible in a single request (untested code) :

from django.db.models import Case, When, Q, F

members = Member.objects.filter(Q(reds__ring__tournament_id=11402)|Q(blues__ring__tournament_id=11402)).annotate(
    red_points=Case(
        When(Q(reds__winner='red') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__win'),
        When(Q(reds__winner='draw') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__draw'),
        When(Q(reds__winner='blue') & Q(reds__red_count_ranking=True), then='reds__trophy_rule__loose'),
    ),
    blue_points=Case(
        When(Q(blues__winner='blue') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__win'),
        When(Q(blues__winner='draw') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__draw'),
        When(Q(blues__winner='red') & Q(blues__blue_count_ranking=True), then='blues__trophy_rule__loose'),
    ),
    points=F('red_points') + F('blue_points')
)

This query will return the sum of all points for red and blue corner athletes during a match. I need the sum of points when one athlete was red and when it was blue, but not for the other athlete of the match. — cgaspoz, Aug 24 '20 at 08:20

Django compute score of matches (workaround to annotate a query after a union)

1 Answers1