rewrite sql query using Django db Api

Question

These are my models in django

class Match(models.Model):
    team_a = models.ForeignKey("Team", related_name="team_a")
    equipo_b = models.ForeignKey("Team", related_name="team_b")
    goals_team_a = models.IntegerField(default=0)
    goals_team_b = models.IntegerField(default=0)
    winner_team = models.ForeignKey("Team", related_name="winner_team", null=True)
    match_type = models.CharField(choices=match_type_choices, max_length=100, null=False)
    match_played = models.BooleanField(default=False)
    date = models.DateField()

class Team(models.Model):
    name = models.CharField(max_length=200)
    group = models.CharField(choices=group_choices, max_length=1, null=False)
    matches_played = models.IntegerField(default=0)
    matches_won = models.IntegerField(default=0)
    matches_lost = models.IntegerField(default=0)
    matches_tied = models.IntegerField(default=0)
    goals_in_favor = models.IntegerField(default=0)
    goals_agaiinst = models.IntegerField(default=0)
    points = models.IntegerField(default=0)
    url_flag = models.CharField(max_length=500)

And I have the following sentence in raw sql:

select 
  (select sum(goals_team_a) from match where team_a_id=9) +
  (select sum(goals_team_b) from match where team_b_id=9) goals_in_favor,
  (select sum(goals_team_b) from match where team_a_id=9) +
  (select sum(goals_team_a) from match where team_b_id=9) goles_against
;

I'm looking for an efficient way to rewrite this query using Django QuerySet API, but can't find a way to even do it, I have a clue that I have to use the annotate() function, but don't know how.

score 0 · Accepted Answer · answered Jul 11 '14 at 19:32

Finally came up with a resolution:

def count_goals_in_favor(team):
    goals_home = Partido.objects.filter(team_a=team).extra(
        select={"total_goals": "SUM(goals_team_a)"}).values("total_goals")
    goals_away = Partido.objects.filter(team_b=team).extra(
        select={"total_goals": "SUM(goals_team_b)"}).values("total_goals")
   return goals_home[0]['total_goals'] + goals_away[0]['total_goals']


def count_goals_against(team):
    goals_home = Partido.objects.filter(team_a=team).extra(
        select={"total_goals": "SUM(goals_team_b)"}).values("total_goals")
    goals_away = Partido.objects.filter(team_b=team).extra(
        select={"total_goals": "SUM(goals_team_a)"}).values("total_goals")
    return goals_home[0]['total_goals'] + goals_away[0]['total_goals']

class Team(models.Model):
    pass # All the code I put in my question
    def goals_difference(self):
        return self.goals_in_favor - self.goals_against

class Match(models.Model):
    pass # all the code i put in my question
    def save(self):
        self.team_a.goals_in_favor = count_ goals_in_favor(self.team_a)
        self.team_a.goals_against = count_goals_against(self.team_a)

rewrite sql query using Django db Api

1 Answers1