Mysql 错误：此查询集包含对外部查询的引用，并且只能在子查询中使用

这可能是一个很容易发现的错误，但我不是最熟悉Django查询的人，所以我花了几个小时努力解决这个问题.

我有一个Django Rest应用程序，在那里我有两个模型:

class Student(models.Model):
    name = models.CharField(max_length=20)
    status = models.CharField(max_length=4, blank=True, default="UNK", choices=STATUS_TYPES)
    started_date = models.DateTimeField(null=False, auto_now_add=True)


class StudentStatusHistory(BaseTimestampModel):
    """Table of status changes for Students"""
    student = models.ForeignKey(
        "core.Student", on_delete=models.CASCADE, related_name="StatusHistory"
    )
    status = models.CharField(
        max_length=4, blank=True, default="UNK", choices=STATUS_TYPES
    )
    created_on = models.DateTimeField(null=False, auto_now_add=True)

供参考:

STATUS_TYPES = [("UNK", "Unknown"), (“PASS", “Passed"), (“FAIL", “Failed")]

我正在try 编写一个查询，以计算在一周或更短的时间窗口内有多少学生的状态更改为Pass.

例如，如果Student.started_date是2023-07-15 12:22:22,StudentStatusHistory.created_on字段是2023-07-21 12:22:22，对于一个Student.status等于PASS的学生，我想计算这个学生，因为他们在不到一周的时间内通过了考试(在本例中是6天).

另外，我只想计算当月开始的学生的这一数字.

到目前为止，我所拥有的是:

current_month_start = datetime(datetime.today().year, datetime.today().month, 1)

# Subquery to filter StudentStatusHistory objects for each Student object within a week or less
pass_within_week_subquery = StudentStatusHistory.objects.filter(
            student_id=OuterRef(“pk"),
            status="PASS",
            created_on__range=(OuterRef("started_date"), OuterRef(“started_date") + timedelta(weeks=1))
        )

# Subquery to filter Student objects created within the current month
student_within_month_subquery = Student.objects.filter(
            started_date__gte=current_month_start,
            pk=OuterRef(“pk"),
        )

# Main queryset to annotate Student objects with pass_within_week count
student_objects_with_pass_within_week = Student.objects.annotate(
            pass_within_week_count=Subquery(
                pass_within_week_subquery.annotate(count_pass_within_week=Count("pk")).values(“count_pass_within_week"),
                output_field=IntegerField()),
            is_within_month=Exists(student_within_month_subquery),
        )

# Count the number of student objects that satisfy the condition
count_student_pass_within_week = student_objects_with_pass_within_week.filter(pass_within_week_count__gt=0,
is_within_month=True).count()

return Response(data= count_student_pass_within_week)

(我不确定python格式有什么问题，但请忽略或编辑这一部分)

一切似乎都很顺利，直到关于返回错误的计数的最后一行:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

我读了很多关于OuterRef、子查询等的内容，我以为在主查询集中使用它们可以解决这个问题，但显然不能.

有什么主意吗？