如何从QuerySet高效获取指定城市高校的最新符合条件Course？

模型定义 #

class University(models.Model):
    name = models.CharField("name", max_length = 48)
    city = models.ForeignKey(City, on_delete = models.CASCADE) 

class Course(models.Model):
    name = models.CharField("course name", max_length = 48)
    university = models.ForeignKey(University, on_delete = models.CASCADE)
    students = models.ManyToManyField(Student, related_name = "%(class)s_name", related_query_name = "student_course_qs", blank = True)
    online = models.BooleanField("online course", default = False)
    semester = models.PositiveIntegerField("choose 1-6", default = 1, choices = [(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6)])
    time_update = models.DateTimeField("last info update", auto_now = True)

限制条件 #

• 若一个Course关联了students，则它不能是online课程，反之亦然。

需求 #

获取指定城市下所有University中，各高校的最新Course条目里，属于第3学期且关联了students的所有Course列表。

原实现及问题 #

原代码通过循环大学分组查询，会产生N+1次数据库查询（1次查大学，N次查每个大学的最新课程），性能较差：

universities = University.objects.filter(city = City.objects.first())

wanted = []
for univ in universities:
    c = Course.objects.filter(university = univ).order_by("time_update").last()
    if c.semester == 3 and not c.online:
        wanted.append(c.id)

selected_courses = Course.objects.filter(id__in = wanted)

优化方案 #

方案一：使用子查询（Subquery） #

通过子查询直接获取每个大学的最新课程ID，再一次性筛选符合条件的课程，仅需2次数据库查询：

from django.db.models import Subquery, OuterRef

# 子查询：获取每个大学最新的Course的ID
latest_course_subquery = Course.objects.filter(
    university=OuterRef('pk')
).order_by('-time_update').values('id')[:1]

# 筛选指定城市大学的最新课程，并匹配学期、非在线（关联学生）条件
selected_courses = Course.objects.filter(
    id__in=Subquery(latest_course_subquery),
    university__city=City.objects.first(),
    semester=3,
    online=False  # 根据限制条件，关联students等价于online=False
)

方案二：通过聚合标注（annotate）获取最新时间 #

先标注每个大学的最新课程更新时间，再根据时间匹配课程，同样仅需2次查询：

from django.db.models import Max

# 标注指定城市每个大学的最新课程更新时间
university_latest = University.objects.filter(
    city=City.objects.first()
).annotate(
    latest_course_time=Max('course__time_update')
).values('pk', 'latest_course_time')

# 构造查询条件，匹配大学、最新更新时间，以及课程要求
selected_courses = Course.objects.filter(
    university__in=[univ['pk'] for univ in university_latest],
    time_update__in=[univ['latest_course_time'] for univ in university_latest],
    semester=3,
    online=False
)

这两种方案都避免了循环查询带来的性能问题，大幅减少数据库交互次数。

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