Resolve "[SQL] Monster query on personal notes"

Closes #128 (closed)

aleksis/apps/alsijil/model_extensions.py

@@ -279,66 +279,63 @@ def generate_person_list_with_class_register_statistics(
     if persons is None:
         persons = self.members.all()
 
-    persons = (
-        persons.filter(
-            personal_notes__groups_of_person=self,
-            personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
-        )
-        .distinct()
-        .annotate(
-            absences_count=Count(
-                "personal_notes__absent",
-                filter=Q(
-                    personal_notes__absent=True,
-                    personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
-                )
-                & (
-                    Q(personal_notes__lesson_period__lesson__groups=self)
-                    | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
-                ),
+    persons = persons.filter(
+        personal_notes__groups_of_person=self,
+        personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
+    ).distinct()
+    persons = persons.annotate(
+        absences_count=Count(
+            "personal_notes__absent",
+            filter=Q(
+                personal_notes__absent=True,
+                personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
+            )
+            & (
+                Q(personal_notes__lesson_period__lesson__groups=self)
+                | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
             ),
-            excused=Count(
-                "personal_notes__absent",
-                filter=Q(
-                    personal_notes__absent=True,
-                    personal_notes__excused=True,
-                    personal_notes__excuse_type__isnull=True,
-                    personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
-                )
-                & (
-                    Q(personal_notes__lesson_period__lesson__groups=self)
-                    | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
-                ),
+        ),
+        excused=Count(
+            "personal_notes__absent",
+            filter=Q(
+                personal_notes__absent=True,
+                personal_notes__excused=True,
+                personal_notes__excuse_type__isnull=True,
+                personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
+            )
+            & (
+                Q(personal_notes__lesson_period__lesson__groups=self)
+                | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
             ),
-            unexcused=Count(
-                "personal_notes__absent",
-                filter=Q(
-                    personal_notes__absent=True,
-                    personal_notes__excused=False,
-                    personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
-                )
-                & (
-                    Q(personal_notes__lesson_period__lesson__groups=self)
-                    | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
-                ),
+        ),
+        unexcused=Count(
+            "personal_notes__absent",
+            filter=Q(
+                personal_notes__absent=True,
+                personal_notes__excused=False,
+                personal_notes__lesson_period__lesson__validity__school_term=self.school_term,
+            )
+            & (
+                Q(personal_notes__lesson_period__lesson__groups=self)
+                | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
             ),
-            tardiness=Sum(
-                "personal_notes__late",
-                filter=(
-                    Q(personal_notes__lesson_period__lesson__groups=self)
-                    | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
-                ),
+        ),
+        tardiness=Sum(
+            "personal_notes__late",
+            filter=(
+                Q(personal_notes__lesson_period__lesson__groups=self)
+                | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
             ),
-            tardiness_count=Count(
-                "personal_notes",
-                filter=~Q(personal_notes__late=0)
-                & Q(personal_notes__lesson_period__lesson__validity__school_term=self.school_term,)
-                & (
-                    Q(personal_notes__lesson_period__lesson__groups=self)
-                    | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
-                ),
+        ),
+        tardiness_count=Count(
+            "personal_notes",
+            filter=~Q(personal_notes__late=0)
+            & Q(personal_notes__lesson_period__lesson__validity__school_term=self.school_term,)
+            & (
+                Q(personal_notes__lesson_period__lesson__groups=self)
+                | Q(personal_notes__lesson_period__lesson__groups__parent_groups=self)
             ),
-        )
+        ),
     )
 
     for extra_mark in ExtraMark.objects.all():