get first_diffusion with annotate() and RawSQLQ (not .extra())

diff --git a/emissions/models.py b/emissions/models.py
index 37326e10f4aea0ca53224a98cb9c4360b11a0897..44eba58e78fe8468c102857c29c4c8a73392a55c 100644 (file)
--- a/emissions/models.py
+++ b/emissions/models.py
@@ -7,6 +7,7 @@ from django.conf import settings
 from django.core.exceptions import ObjectDoesNotExist
 from django.core.validators import URLValidator
 from django.db import models
+from django.db.models.expressions import RawSQL
 from django.db.models.signals import post_delete, pre_save
 from django.dispatch.dispatcher import receiver
 from django.forms import fields
@@ -247,17 +248,14 @@ class Emission(models.Model):
     def get_sorted_episodes(self):
         return (
             self.episode_set.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'],
+            .annotate(
+                first_diffusion=RawSQL(
+                    '''SELECT MIN(datetime)
+                         FROM emissions_diffusion
+                        WHERE episode_id = emissions_episode.id
+                          AND emissions_episode.emission_id = emissions_emission.id''',
+                    (),
+                )
             )
             .order_by('-first_diffusion')
         )