Python 3.6/Django 2多表关联查询：扩展用户模型与分组关联实现

First, let’s make sure your Django models are properly defined with correct relationships—this will make your join queries straightforward using Django’s ORM. Based on your table structure, here’s how you’d set them up:

import uuid
from django.db import models

class Profile(models.Model):
    id = models.IntegerField(primary_key=True)
    name_last = models.CharField(max_length=32)
    name_first = models.CharField(max_length=32)
    account_number = models.UUIDField(default=uuid.uuid4, unique=True)

class Group(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    name = models.CharField(max_length=32)

class ProfileGroupLink(models.Model):
    id = models.IntegerField(primary_key=True)
    groupLnk = models.ForeignKey(Group, on_delete=models.CASCADE, db_column='groupLnk')
    # Assuming profileLnk references Profile's account_number (uuid field)
    profileLnk = models.ForeignKey(Profile, on_delete=models.CASCADE, db_column='profileLnk', to_field='account_number')

Note: I added to_field='account_number' to the profileLnk foreign key since your Profile uses an integer primary key but profileLnk targets the UUID account number. Adjust this if profileLnk should reference Profile.id instead (omit to_field in that case, as it defaults to the primary key).

Common Join Scenarios Using Django ORM #

1. Fetch Profiles with Their Associated Groups #

Since this is a many-to-many relationship via your link table, using prefetch_related optimizes queries to avoid the N+1 problem. For cleaner code, you can even define a direct ManyToManyField on either model:

Update the Profile model to add a many-to-many link:

class Profile(models.Model):
    # ... existing fields ...
    groups = models.ManyToManyField(Group, through='ProfileGroupLink', related_name='profiles')

Now you can query profiles and their groups easily:

from myapp.models import Profile

# Get all profiles with their groups (optimized)
profiles = Profile.objects.prefetch_related('groups').all()

# Access groups for each profile
for profile in profiles:
    print(f"{profile.name_first} {profile.name_last} is part of:")
    for group in profile.groups.all():
        print(f"- {group.name}")

2. Fetch Groups with Their Associated Profiles #

Using the related_name='profiles' we added, this becomes just as simple:

from myapp.models import Group

groups = Group.objects.prefetch_related('profiles').all()

for group in groups:
    members = [f"{p.name_first} {p.name_last}" for p in group.profiles.all()]
    print(f"Group '{group.name}' members: {', '.join(members)}")

3. Filter Profiles by Group Attributes #

For example, find all profiles in a group named "Admin":

admin_profiles = Profile.objects.filter(groups__name='Admin').distinct()

Use distinct() to avoid duplicate profile entries if a user is in multiple matching groups.

4. Filter Groups by Profile Attributes #

Find all groups that include a profile with the last name "Smith":

smith_groups = Group.objects.filter(profiles__name_last='Smith').distinct()

Raw SQL for Complex Joins (If Needed) #

If you need full control over the SQL query, use Django’s raw() method:

profiles_with_groups = Profile.objects.raw("""
    SELECT p.id, p.name_first, p.name_last, g.name as group_name
    FROM myapp_profile p
    JOIN myapp_profilegrouplink pl ON p.account_number = pl.profileLnk
    JOIN myapp_group g ON pl.groupLnk = g.id
""")

for entry in profiles_with_groups:
    print(f"{entry.name_first} {entry.name_last} - {entry.group_name}")

Replace myapp with your actual app name.

Key takeaway: Always prefer Django’s ORM relationships (like the ManyToManyField with a through table) when possible—it’s more maintainable, handles database differences, and reduces SQL errors.

内容的提问来源于stack exchange，提问作者7 Reeds