performance: rewrite /listen to minimize queries

This cuts down the page from 521 queries to 3 (in my local dataset).

diff --git a/panikweb/views.py b/panikweb/views.py
index 658c69abbece285cd8fbc7625a111d475beb66ee..b8b3bcfd6c9247b2440be34124fc8fdf82e3d31d 100644 (file)
--- a/panikweb/views.py
+++ b/panikweb/views.py
@@ -427,27 +427,27 @@ class Listen(TemplateView):
     def get_context_data(self, **kwargs):
         context = super(Listen, self).get_context_data(**kwargs)
         context['sectionName'] = "Listen"
-        context['sounds'] = SoundFile.objects.all()[:15]
-        listenable = []
-        x = 0
-        for episode in Episode.objects.all().extra(select={
-                        'first_diffusion': 'emissions_diffusion.datetime',
-                        },
-                        select_params=(False, True),
-                        where=['''datetime = (SELECT MIN(datetime)
-                                                FROM emissions_diffusion
-                                               WHERE episode_id = emissions_episode.id)'''],
-                        tables=['emissions_diffusion'],
-                    ).order_by('-first_diffusion'):
-            if x >= 60:
-                break
-            elif episode.main_sound is None:
-                continue
-            else:
-                x += 1
-                listenable.append(episode)
+        context['episodes'] = Episode.objects.filter(
+                soundfile__podcastable=True, soundfile__fragment=False) \
+                .select_related().extra(select={
+                    'first_diffusion': 'emissions_diffusion.datetime', },
+                    select_params=(False, True),
+                    where=['''datetime = (SELECT MIN(datetime)
+                                            FROM emissions_diffusion
+                                        WHERE episode_id = emissions_episode.id)'''],
+                    tables=['emissions_diffusion'],).order_by('-first_diffusion') [:60]
+
+        # get all related soundfiles in a single query
+        soundfiles = {}
+        for soundfile in SoundFile.objects.select_related().filter(podcastable=True,
+                fragment=False, episode__in=[x.id for x in context['episodes']]):
+            soundfiles[soundfile.episode_id] = soundfile
+
+        # replace dynamic property by a static attribute, to avoid database
+        # lookups
+        for episode in context['episodes']:
+            episode.main_sound = soundfiles.get(episode.id)
 
-        context['episodes'] = listenable
         return context
 
 listen = Listen.as_view()