Good day. I wish to annotate my model with information from a different table.
class CompetitionTeam(models.Model):
competition_id = models.ForeignKey('Competition', on_delete=models.CASCADE, to_field='id', db_column='competition_id')
team_id = models.ForeignKey('Team', on_delete=models.CASCADE, to_field='id', null=True, db_column='team_id')
...
class Team(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=30)
teamleader_id = models.ForeignKey('User', on_delete=models.CASCADE, to_field='id', db_column='teamleader_id')
...
class Competition(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=30)
...
Looping through my competitions, I wish to retrieve the list of competitionteam objects to be displayed with the relevant team's name. I tried:
CompetitionTeam.objects.filter(competition_id=_competition.id).filter(team_id__in=joined_team_ids).annotate(name=...)
-where instead of the ellipses I put Subquery expressions in. However, I'm unsure of how to match the team_id variable. eg.
*.anotate(name=Subquery(Team.objects.filter(id=competitionteam.team_id)).values('name'))
Related is the question: Django annotate field value from another model but I am unsure of how to implement that in this case. In that case, in place of mymodel_id, I used team_id but it only had parameters from the Team object, not my competition team object. I didn't really understand OuterRef but here is my attempt that failed:
CompetitionTeam.objects.filter(competition_id=_competition.id).filter(team_id__in=joined_team_ids).annotate(name=Subquery(Team.objects.get(id=OuterRef('team_id'))))
"Error: This queryset contains a reference to an outer query and may only be used in a subquery."