include shows with recent soundfiles on homepage

diff --git a/panikweb_studioneau/views.py b/panikweb_studioneau/views.py
index 27edd1daffddd890bb0a6939d0c1890e78bc2a42..9d7044bcfe540ebf102fc883f478d5f27a54231f 100644 (file)
--- a/panikweb_studioneau/views.py
+++ b/panikweb_studioneau/views.py
@@ -4,6 +4,7 @@ import random
 import panikweb.utils
 import panikweb.views
 from django.conf import settings
+from django.db.models.expressions import RawSQL
 from django.utils.timezone import now
 from django.utils.translation import ugettext_lazy as _
 from django.views.generic.base import TemplateView
@@ -14,30 +15,22 @@ from emissions.utils import period_program
 class Home(panikweb.views.Home):
     def get_context_data(self, **kwargs):
         context = super().get_context_data(**kwargs)
-        # order by creation timestamp
+        # get shows with a recent sound
         context['shows'] = (
             Emission.objects.filter(archived=False)
             .exclude(categories__slug='collection')
-            .order_by('-creation_timestamp')
-        )
-        # or by latest diffusion (this requires episodes)
-        context['shows'] = (
-            Emission.objects.filter(archived=False)
-            .exclude(categories__slug='collection')
-            .extra(
-                select={
-                    'latest_diffusion': 'emissions_diffusion.datetime',
-                },
-                select_params=(False, True),
-                where=[
-                    '''datetime = (SELECT MAX(datetime)
-                                     FROM emissions_diffusion, emissions_episode
-                                    WHERE episode_id = emissions_episode.id
-                                      AND emission_id = emissions_emission.id)'''
-                ],
-                tables=['emissions_diffusion', 'emissions_episode'],
+            .annotate(
+                latest_soundfile_timestamp=RawSQL(
+                    '''SELECT MAX(emissions_soundfile.creation_timestamp)
+                         FROM emissions_soundfile,
+                              emissions_episode
+                        WHERE emissions_soundfile.episode_id = emissions_episode.id
+                          AND emissions_episode.emission_id = emissions_emission.id''',
+                    (),
+                )
             )
-            .order_by('-latest_diffusion')
+            .exclude(latest_soundfile_timestamp__isnull=True)
+            .order_by('-latest_soundfile_timestamp')
             .distinct()
         )