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.