views: requête de liste des groupes/semaines opti pour PostgreSQL

1 files changed, 35 insertions, 17 deletions

diff --git a/views.py b/views.py
index e45ece3..88c4665 100644
--- a/views.py
+++ b/views.py
@@ -57,23 +57,41 @@ def group_list(request, year_slug, timetable_slug):
 
     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:
-        for group_week in groups_weeks:
-            if group.corresponds_to(group_week["groups__mention"],
-                                    group_week["groups__semester"],
-                                    group_week["groups__subgroup"]):
-                if not hasattr(group, "weeks"):
-                    group.weeks = []
-
-                date, _ = get_week(group_week["year"], group_week["week"])
-                if date not in group.weeks:
-                    group.weeks.append(date)
-
-        if hasattr(group, "weeks"):
-            group.weeks.sort()
+
+    if connection.vendor == "postgresql":
+        weeks_sreq = RawSQL("""
+SELECT ARRAY_AGG(DISTINCT DATE_TRUNC('week', begin)) FROM (
+  SELECT u2.begin from edt_group u0
+    LEFT OUTER JOIN edt_course_groups u1 ON (u0.id = u1.group_id)
+    LEFT OUTER JOIN edt_group u2 ON (u1.course_id = u2.id)
+  WHERE u0.source_id = edt_group.source_id AND
+    u0.mention = edt_group.mention AND
+    u0.semester = edt_group.semester AND
+    u0.subgroup = SUBSTRING(edt_group.subgroup, 1, LENGTH(u0.subgroup)) AND
+    u2.begin >= DATE_TRUNC('week', NOW()) AND
+    u2.begin < DATE_TRUNC('week', NOW() + '1 MONTH'::INTERVAL)
+  ORDER BY u2.begin
+) weeks""", [])
+        groups = groups.annotate(weeks=weeks_sreq)
+    else:
+        groups_weeks = Course.objects.get_weeks(begin__gte=start,
+                                                begin__lt=end,
+                                                groups__in=groups)
+
+        for group in groups:
+            for group_week in groups_weeks:
+                if group.corresponds_to(group_week["groups__mention"],
+                                        group_week["groups__semester"],
+                                        group_week["groups__subgroup"]):
+                    if not hasattr(group, "weeks"):
+                        group.weeks = []
+
+                    date, _ = get_week(group_week["year"], group_week["week"])
+                    if date not in group.weeks:
+                        group.weeks.append(date)
+
+            if hasattr(group, "weeks"):
+                group.weeks.sort()
 
     return render(request, "group_list.html",
                   {"timetable": timetable, "elements": groups})