Annotate the current reverse relation regarding the closest date but not in the future

Using Django Using the ORM
1

Hello,

Here are my three models. The EpisodeFacility allows to group a Facility within an Episode for a given date (active_since). I can have a EpisodeFacility in the futur, but the idea is to select the current facility for an episode by using the closest active_since but not in the future.

I’m using DRF so I need to filter the result by current_episode_facility

class Facility(translation.SaveLookupTermsMixin, models.Model):

    address = models.OneToOneField(Address, null=True, on_delete=models.SET_NULL)
    name = models.CharField(_("name"), blank=True, max_length=100)

class Episode(models.Model):
    pass

class EpisodeFacility(models.Model):
    created_at = models.DateTimeField(_("created at"), auto_now_add=True)
    active_since = models.DateTimeField(_("active since"), db_index=True)
    episode = models.ForeignKey(
        Episode, on_delete=models.PROTECT, related_name="facilities"
    )
    facility = models.ForeignKey(
        Facility, on_delete=models.PROTECT, related_name="+"
    )

Here is the query i’ve written with a subquery to get the current facility.

class EpisodeQuerySet(models.QuerySet):
    def with_facility(self):
        # Get current facility as SubQuery
        current_facility = EpisodeFacility .objects.filter(
            episode_id=OuterRef("pk"), active_since__lte=Now()
        ).order_by("-active_since")

        return self.annotate(
            # add current facility to the episode queryset
            current_episode_facility=Subquery(
                current_facility.values("facility")[:1]
            ),
            facility_name=Subquery(
                current_facility.values("facility__name")[:1]
            ),
        )

This query works well but i find it a little bit slow. I use DRF so there is the count + the query itself. Is there a way to replace the Subquery by a joining with a FilteredRelation ? With the FilteredRelation I can specify the condition but not select a single EpisodeFacility (I think).

Thanks for your help.

Kind Regards.

Joel