如何在Django中结合外键值与外键修改查询关联表数据？

Hey there! Let's walk through how to work with Django queries for your League and Team models, focusing on league revenue data and handling foreign key changes. First, let's fix up the Team model to include the proper foreign key relationship (since it was truncated in your example):

class Team(models.Model):
    name = models.TextField(max_length=20)
    year = models.IntegerField()  # Assuming this was a year field—adjust as needed
    league = models.ForeignKey(League, on_delete=models.CASCADE, related_name="teams")
    # Added `related_name="teams"` to make reverse queries cleaner (no more clunky `team_set`)

Now let's break down common query scenarios:

1. Get League Revenue from a Team #

If you have a Team instance and need the revenue of its associated league, it's super straightforward:

# Fetch a specific team (tweak the filter to match your use case)
my_team = Team.objects.get(name="Sunset Warriors")

# Access the linked league's revenue directly
league_revenue = my_team.league.revenue
print(f"The league for {my_team.name} has a revenue of ${league_revenue:,}")

For bulk queries (getting all teams paired with their league revenue), use select_related to avoid extra database hits:

# Get a QuerySet of dictionaries with team name and associated league revenue
teams_and_revenue = Team.objects.select_related('league').values('name', 'league__revenue')

for item in teams_and_revenue:
    print(f"Team: {item['name']}, League Revenue: ${item['league__revenue']:,}")

2. Get Teams & Revenue from a League #

To pull all teams belonging to a league, along with the league's own revenue:

# Fetch a specific league
premier_league = League.objects.get(id=1)

# Get all teams in this league (using the `related_name="teams"` we set)
league_teams = premier_league.teams.all()

for team in league_teams:
    print(f"Team: {team.name}, League Revenue: ${premier_league.revenue:,}")

For a more structured bulk summary (like league revenue plus team count), use prefetch_related and annotate:

from django.db.models import Count

# Get each league with its revenue and number of associated teams
league_summary = League.objects.prefetch_related('teams').annotate(team_count=Count('teams')).values('id', 'revenue', 'team_count')

for league in league_summary:
    print(f"League ID: {league['id']}, Revenue: ${league['revenue']:,}, Teams: {league['team_count']}")

3. Querying After Updating a Team's Foreign Key #

If you change a team's associated league, you can immediately access the new league's revenue—just remember to save the team first:

# Fetch the team and the new league to associate
my_team = Team.objects.get(name="Sunset Warriors")
new_league = League.objects.get(id=3)

# Update the foreign key
my_team.league = new_league
my_team.save()

# Get the updated league revenue
new_revenue = my_team.league.revenue
print(f"New league revenue for {my_team.name}: ${new_revenue:,}")

If you're working with an instance that might have been updated elsewhere, use refresh_from_db() to ensure you get the latest data from the database:

my_team.refresh_from_db()
latest_revenue = my_team.league.revenue

内容的提问来源于stack exchange，提问作者Ian McInerney