On cache les groupes qui n’ont plus de cours

La requête est assez longue à s’effectuer sur SQLite, mais pas sur PostgreSQL

2 files changed, 10 insertions, 3 deletions

diff --git a/models.py b/models.py
index 15f378a..c122bfd 100644
--- a/models.py
+++ b/models.py
@@ -16,7 +16,7 @@
 from functools import reduce
 
 from django.db import models
-from django.db.models import Manager, Q
+from django.db.models import Count, Manager, OuterRef, Q, Subquery
 from django.db.models.functions import ExtractWeek, ExtractYear
 from django.utils import timezone
 from django.utils.text import slugify
@@ -104,6 +104,13 @@ class GroupManager(Manager):
                                           mention=group.mention,
                                           source=group.source)
 
+    def get_relevant_groups(self, start, **criteria):
+        courses = Course.objects.filter(groups=OuterRef("pk"), begin__gte=start) \
+                                .only("pk")[:1]
+        return self.get_queryset().annotate(c=Subquery(courses,
+                                                       output_field=models.IntegerField())) \
+                                  .filter(c__isnull=False, **criteria).order_by("name")
+
 
 class Group(SlugModel):
     objects = GroupManager()
diff --git a/views.py b/views.py
index 10dcfa2..9b53f3a 100644
--- a/views.py
+++ b/views.py
@@ -15,7 +15,7 @@
 
 import datetime
 
-from django.db.models import Count, Max
+from django.db.models import Max
 from django.db.models.functions import ExtractWeek, ExtractYear, Length
 from django.http import Http404
 from django.shortcuts import get_object_or_404, render
@@ -39,11 +39,11 @@ def mention_list(request, year_slug):
 
 def group_list(request, year_slug, timetable_slug):
     timetable = get_object_or_404(Timetable, year__slug=year_slug, slug=timetable_slug)
-    groups = Group.objects.filter(source=timetable.source, hidden=False).order_by("name")
 
     start, _ = get_week(*get_current_week())
     end = start + datetime.timedelta(weeks=4)
 
+    groups = Group.objects.get_relevant_groups(start, source=timetable.source, hidden=False)
     groups_weeks = Course.objects.get_weeks(begin__gte=start, begin__lt=end, groups__in=groups)
 
     for group in groups: