Django MySQL外键查询问题：针对给定Shows模型的技术咨询

Alright, let's tackle foreign key queries with your Django Shows model in MySQL. First, I'll start by showing you how to set up proper foreign key relationships, then walk through common query patterns you'll need.

Since your Shows model uses a custom CharField (show_key) as its primary key instead of Django's default auto-increment id, you'll need to explicitly reference this field when defining foreign keys in related models.

For example, let's say we have a Performers model that links to shows. Here's how to define the foreign key correctly:

from django.db import models

class Performers(models.Model):
    performer_name = models.CharField(max_length=100)
    # Foreign key linking to Shows' show_key field
    show = models.ForeignKey(
        Shows,
        on_delete=models.CASCADE,  # Choose based on your business logic (CASCADE/PROTECT/SET_NULL)
        to_field='show_key',       # Critical: specifies we're linking to Shows' custom primary key
        related_name='performers', # Optional but recommended: custom name for reverse queries
        db_column='show_key'       # Optional: matches the column name in MySQL for clarity
    )

Key notes here:

• to_field='show_key' is mandatory—without it, Django will try to link to a default id field that doesn't exist in your Shows model.
• related_name makes reverse queries more intuitive (instead of the default performers_set).

Forward Queries (From Related Model to Shows) #

If you have a Performers instance, you can directly access its linked Shows data:

# Get a single performer and their show details
performer = Performers.objects.get(performer_name="Jane Smith")
show_info = performer.show
print(f"Show Date: {show_info.show_date}, Venue: {show_info.show_venue}")

# Avoid N+1 queries with select_related()
# This fetches all performers AND their linked shows in one MySQL query
performers_with_shows = Performers.objects.select_related('show').all()
for performer in performers_with_shows:
    print(f"{performer.performer_name} is playing at {performer.show.show_venue}")

Reverse Queries (From Shows to Related Models) #

Using the related_name we defined, you can fetch all related records from a Shows instance:

# Get all performers for a specific show
show = Shows.objects.get(show_key="SHOW007")
all_performers = show.performers.all()
for performer in all_performers:
    print(f"Performer: {performer.performer_name}")

# Filter performers by show attributes using double underscores (__)
# Get all performers playing in New York
nyc_performers = Performers.objects.filter(show__show_city="New York")

• Storage Engine: Ensure your MySQL tables use InnoDB (the default in Django), as MyISAM does not support foreign key constraints. You can explicitly set this in your model's Meta class if needed:

  class Shows(models.Model):
      # ... your existing fields
      class Meta:
          db_table = 'shows'
          engine = 'InnoDB'
  

• Data Type Consistency: The foreign key field in your related model must match the data type of show_key (a CharField with max_length=7). Mismatched types will cause MySQL errors.
• On-Delete Behavior: Choose on_delete carefully:
  • CASCADE: Deleting a show deletes all linked performers (good for dependent records).
  • PROTECT: Prevents deleting a show if it has linked performers (avoids accidental data loss).
  • SET_NULL: Sets the foreign key to NULL when the show is deleted (requires the foreign key field to have null=True).

Aggregate: Count Shows per City #

from django.db.models import Count

# Get the number of shows in each city, ordered by most to least
city_show_counts = Shows.objects.values('show_city').annotate(total_shows=Count('show_key')).order_by('-total_shows')
# Returns a list like: [{'show_city': 'Los Angeles', 'total_shows': 22}, ...]

Aggregate: Count Shows per Performer #

from django.db.models import Count

# How many shows has each performer been in?
performer_show_counts = Performers.objects.values('performer_name').annotate(total_shows=Count('show')).order_by('-total_shows')

内容的提问来源于stack exchange，提问作者Brent Vaalburg