如何高效查询多个Django模型？寻求相关实现示例

嘿，我太懂你找这类场景化查询示例的痛苦了——Django官方文档虽然全面，但针对销售团队、周目标这类特定业务的组合查询，确实得结合实际需求来琢磨。先把你提到的模型补全得更贴合业务逻辑（毕竟你没写完Team模型，我补了关联关系和必要字段），然后直接上几个常用的高效查询场景，都是实际项目里能用的：

先补全业务模型（贴合你的需求） #

class Employee(models.Model):
    employee_id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=100)
    email = models.EmailField()
    status = models.CharField(max_length=100)  # 比如"在职"/"离职"
    team = models.ForeignKey('Team', on_delete=models.CASCADE, related_name='employees')

class Team(models.Model):
    team_id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=100)
    manager = models.ForeignKey(Employee, on_delete=models.SET_NULL, null=True, related_name='managed_teams')

class WeeklyTarget(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='weekly_targets')
    week_start = models.DateField(db_index=True)  # 加索引提升时间范围查询速度
    target_amount = models.DecimalField(max_digits=10, decimal_places=2)

class ActualOutput(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='actual_outputs')
    week_start = models.DateField(db_index=True)
    actual_amount = models.DecimalField(max_digits=10, decimal_places=2)

1. 查询某团队在职员工的周目标&实际产出对比（避免N+1查询） #

这是最常用的场景，用select_related和prefetch_related一次性拉取关联数据，同时在数据库层面计算完成率，不用把数据拉到内存再处理：

from django.db.models import F, ExpressionWrapper, DecimalField, Prefetch
from datetime import date

# 替换成你要查询的团队名称
target_team = Team.objects.get(name="销售一部")
# 替换成本周的起始日期（比如周一）
current_week_start = date(2024, 5, 20)

employee_performance = Employee.objects.filter(
    team=target_team,
    status="在职"
).select_related('team')  # 提前拉取关联的Team数据
.prefetch_related(
    # 只拉取本周的目标和产出，减少不必要的数据
    Prefetch('weekly_targets', queryset=WeeklyTarget.objects.filter(week_start=current_week_start), to_attr='current_week_target'),
    Prefetch('actual_outputs', queryset=ActualOutput.objects.filter(week_start=current_week_start), to_attr='current_week_actual')
).annotate(
    target_amount=F('current_week_target__target_amount'),
    actual_amount=F('current_week_actual__actual_amount'),
    completion_rate=ExpressionWrapper(
        (F('actual_amount') / F('target_amount')) * 100,
        output_field=DecimalField(max_digits=5, decimal_places=2)
    )
).values('name', 'email', 'target_amount', 'actual_amount', 'completion_rate')

# 遍历结果
for emp in employee_performance:
    print(f"{emp['name']} | 目标: {emp['target_amount']} | 实际: {emp['actual_amount']} | 完成率: {emp['completion_rate']}%")

2. 查询全公司各团队的整体目标完成情况 #

用分组统计annotate，直接算出每个团队的总目标、总产出和平均完成率：

from django.db.models import Sum, Avg, Q

team_overview = Team.objects.annotate(
    total_weekly_target=Sum('employees__weekly_targets__target_amount', filter=Q(employees__weekly_targets__week_start=current_week_start)),
    total_weekly_actual=Sum('employees__actual_outputs__actual_amount', filter=Q(employees__actual_outputs__week_start=current_week_start)),
    avg_completion_rate=Avg(
        ExpressionWrapper(
            (F('employees__actual_outputs__actual_amount') / F('employees__weekly_targets__target_amount')) * 100,
            output_field=DecimalField(max_digits=5, decimal_places=2)
        ),
        filter=Q(employees__weekly_targets__week_start=current_week_start, employees__actual_outputs__week_start=current_week_start)
    )
).values('name', 'total_weekly_target', 'total_weekly_actual', 'avg_completion_rate')

for team in team_overview:
    print(f"团队: {team['name']} | 总目标: {team['total_weekly_target']} | 总产出: {team['total_weekly_actual']} | 平均完成率: {team['avg_completion_rate']:.2f}%")

3. 查询本周未完成目标的在职员工 #

用过滤条件直接筛选出未达标的员工，同样用关联查询提升效率：

underperformers = Employee.objects.filter(
    status="在职",
    weekly_targets__week_start=current_week_start,
    actual_outputs__week_start=current_week_start,
    actual_outputs__actual_amount__lt=F('weekly_targets__target_amount')
).select_related('team').values('name', 'team__name', 'weekly_targets__target_amount', 'actual_outputs__actual_amount')

print("本周未完成目标的员工:")
for emp in underperformers:
    print(f"{emp['name']} ({emp['team__name']}) | 目标: {emp['weekly_targets__target_amount']} | 实际: {emp['actual_outputs__actual_amount']}")

几个高效查询的小贴士 #

• 优先用select_related（处理一对一/正向外键）和prefetch_related（处理多对多/反向外键），避免N+1查询问题，这是提升查询效率的关键
• 用F()表达式在数据库层面计算字段，不要把大量数据拉到Python内存里处理，速度会快很多
• 对于经常按时间范围查询的字段（比如week_start），一定要加db_index=True，索引能大幅提升查询速度
• 如果查询逻辑复杂且频繁使用，可以封装成模型的Manager方法，方便复用

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