如何使用Django ORM从三个连接的模型中进行这个非常复杂的查询

Question

大家好。希望你做得很好。我是Django的新手，在帮助一个小型应用程序项目的同时，尝试学习RESTful开发的基础知识。我们目前希望我们的一些模型根据我们提交给它们的数据进行相应的更新，方法是使用Django ORM和其中一些模型与OneToMany关系共享的字段。目前，有一个非常困难的查询，我必须为我的一个字段做一个自动更新的过滤器。首先，让我解释一下模型。这不是真的，而是一个应该同样工作的替身：

首先，我们有一个Report模型，它是教师对学生的报告：

class Report(models.Model): 
status = models.CharField(max_length=32, choices=Statuses.choices, default=Statuses.created,)
student = models.ForeignKey(Student, on_delete=models.CASCADE,)
headroom_teacher = models.ForeignKey(TeacherStaff, on_delete=models.CASCADE,)
# Various dates
results_date = models.DateTimeField(null=True, blank=True)
report_created = models.DateTimeField(null=True, blank=True)
.
#Other fields that don't matter

这里我们有两个相关的模型，它们是student和headroom_teacher。没有必要展示他们的模型，但他们与下两个模型的关系非常重要。我们还有一个Exams型号：

class Exams(models.Model):
student = models.ForeignKey(student, on_delete=models.CASCADE,)     
headroom_teacher = models.ForeignKey(TeacherStaff, on_delete=models.CASCADE,)

# Various dates
results_date = models.DateTimeField(null=True, blank=True)
initial_exam_date = models.DateTimeField(null=True, blank=True)
.
#Other fields that don't matter

正如你所看到的，这个应用程序的目的类似于报告学生在完成一些考试后的表现，每个报告都是由老师为特定学生编写的，说明他在这些考试中的表现。最后，我们有一个名为StudentMood的最终模型，旨在显示学生根据考试状态的感受：

class StudentMood(models.Model):
report = models.ForeignKey(Report, on_delete=models.CASCADE,)
student_status = models.CharField(
max_length=32, choices=Status.choices,
default=None, null=True, blank=False)
headroom_teacher = models.ForeignKey(TeacherStaff, on_delete=models.CASCADE,)

有了这三种模式，我们就找到了问题的症结所在。我们可能的student_status选项之一被称为Anxious for results，我们相信学生在已经完成考试并等待结果的时候会有这种感觉。

我想使用一个自定义管理器自动将我的student_status设置为该值，该管理器会考虑报告完成的日期或数据输入的日期。我相信这可以通过在考虑initial_exam_date的情况下进行查询来实现。

我已经设置了自定义管理器，唯一缺少的就是这个查询。我别无选择，只能使用Django的ORM。然而，我提出了一个近似的原始SQL查询，我不确定它是否可以：

SELECT student_mood.id AS student_mood_id FROM
school_student_mood LEFT JOIN
school_reports report
ON student_mood.report_id = report.id AND student_mood.headroom_teacher_id = report.headroom_teacher_id
JOIN school_exams exams
ON report.headroom_teacher_id = exams.headroom_teacher_id 
AND report.student_id = exams.student_id
AND exams.results_date > date where the student_mood or report data is entered, I guess 

这就是我来寻求帮助的原因。有人能告诉我们如何将其转换为一个单一的查询吗？

Answer 1

没有环境设置，也不知道您想要从数据中得到什么。这是一个良好的开端。

一般来说，Django ORM不适合这些类型的查询，尝试使用select_relate或预取会导致查询非常复杂且效率低下。

我发现在Django中实现这些类型查询的最佳方法是将您的每一块拼图分解为一个返回"的查询；列表"；然后可以在子查询中使用的ID的数目。

然后你继续工作，直到你有你的最终输出

from django.db.models import Subquery
# Grab the students of any exams where the result_date is greater than a specific date.
student_exam_subquery = Exam.objects.filter(
results_date__gt=timezone.now()
).values_list('student__id', flat=True)
# Grab all the student moods related to reports that relate to our "exams" where the student is anxious
student_mood_subquery = StudentMood.objects.filter(
student_status='anxious',
reports__student__in=Subquery(student_exam_subquery)
).values_list('report__id', flat=True)
# Get the final list of students
Student.objects.values_list('id', flat=True).filter(
reports__id__in=Subquery(student_mood_subquery)
)

现在我怀疑这是否会开箱即用，但这确实是为了让你了解如何以未来开发人员可读且最高效的方式解决这个问题。

Answer 2

所以，我遇到的问题是，学校每个学期都有考试周期，很难只检索学生在这个周期的报告。假设我们有以下数据库：

+-----------+-----------+----------------+-------------------+-------------------+------------------+
|  Student  | Report ID | StudentMood ID | Exam Cycle Status | Initial Exam Date | Report created a |
+-----------+-----------+----------------+-------------------+-------------------+------------------+
| Student 1 |         1 |              1 | Done              | 01/01/2020        | 02/01/2020       |
| Student 2 |         2 |              2 | Done              | 01/01/2020        | 02/01/2020       |
| Student 1 |         3 |              3 | On Going          | 02/06/2020        | 01/01/2020       |
| Student 2 |         4 |              4 | On Going          | 02/06/2020        | 01/01/2020       |
+-----------+-----------+----------------+-------------------+-------------------+------------------+

很明显，我想把我的查询限制在这个周期，比如：

+-----------+-----------+----------------+-------------------+-------------------+------------------+
|  Student  | Report ID | StudentMood ID | Exam Cycle Status | Initial Exam Date | Report created a |
+-----------+-----------+----------------+-------------------+-------------------+------------------+
| Student 1 |         3 |              3 | On Going          | 02/06/2020        | 01/01/2020       |
| Student 2 |         4 |              4 | On Going          | 02/06/2020        | 01/01/2020       |
+-----------+-----------+----------------+-------------------+-------------------+------------------+

现在，你的答案trent真的很有用，但我仍然在以上面的形式检索时遇到问题：

qs_exams = Exams.objects.filter(initial_exam_date__gt=now()).values_list('student__id', flat=True)
qs_report = Report.objects.filter(student__id__in=qs_exams).values_list('id', flat=True)
qs_mood = StudentMood.objects.select_related('report') 
.filter(report__id__in=qs_report).order_by('report__student_id', '-created').distinct()

但这个问题仍然让我了解了整个学年的学生情绪。Sooooo，有什么想法吗？