title: Django: Annotate with field from another table (one-to-many)
body: <p>Good day.
  I wish to annotate my model with information from a different table.</p>
  <pre><code>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)
      ...
  </code></pre>
  <p>Looping through my competitions, I wish to retrieve the list of competitionteam objects to be displayed with the relevant team's name. I tried:</p>
  <pre><code>CompetitionTeam.objects.filter(competition_id=_competition.id).filter(team_id__in=joined_team_ids).annotate(name=...)
  </code></pre>
  <p>-where instead of the ellipses I put Subquery expressions in. However, I'm unsure of how to match the <em>team_id</em> variable. eg.</p>
  <pre><code>*.anotate(name=Subquery(Team.objects.filter(id=competitionteam.team_id)).values('name'))
  </code></pre>
  <p>Related is the question: <a href="https://stackoverflow.com/questions/64391494/django-annotate-field-value-from-another-model">Django annotate field value from another model</a> but I am unsure of how to implement that in this case. In that case, in place of <code>mymodel_id</code>, I used <code>team_id</code> but it only had parameters from the Team object, not my competition team object. I didn't really understand <code>OuterRef</code> but here is my attempt that failed:</p>
  <pre><code>CompetitionTeam.objects.filter(competition_id=_competition.id).filter(team_id__in=joined_team_ids).annotate(name=Subquery(Team.objects.get(id=OuterRef('team_id'))))
  &quot;Error: This queryset contains a reference to an outer query and may only be used in a subquery.&quot;
  </code></pre>