Django how to sort objects by there foregein key relation?-CodePudding

at my Django application I want to display some TV-Show entries. Currently I want to build a filter for TV-Shows sorted by there latest released episodes. But I'm not sure how the query has to look like. This is how I pull episodes for the last 90 days

def tv_show_by_set_just_released(request):
    latest_episodes = TvShowEpisodes.objects.filter(
        release_date__lte=datetime.datetime.today(),
        release_date__gt=datetime.datetime.today()-datetime.timedelta(days=90)).order_by('-release_date')

But how can I reference the actual TV-Show of the Episode now? I want to Sort the Tv-Shows by there latest added episode (release_date) please have a look a my modeling:

class TvShows(models.Model):
    objects = RandomManager()
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    title = models.TextField(verbose_name=_("Title"), blank=False, null=True, editable=False, max_length=255)
    genre_relation = models.ManyToManyField(through='GenreTvShow', to='Genre')
    date_added = models.DateTimeField(auto_now_add=True, blank=True, verbose_name=_("Date Added"))


class TvShowEpisodes(models.Model):
    objects = RandomManager()
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    duration = models.FloatField(verbose_name=_("Duration"), blank=True, null=True, editable=False, max_length=255)
    title = models.TextField(verbose_name=_("Title"), blank=False, null=True, editable=False, max_length=255)
    release_date = models.DateField(verbose_name=_("Release Date"), blank=True, null=True, editable=False)
    track = models.IntegerField(verbose_name=_("Track #"), blank=True, null=True)
    show = models.ForeignKey(TvShows, on_delete=models.CASCADE, related_name='episode_show_relation')
    season = models.ForeignKey(TvShowSeasons, on_delete=models.CASCADE, related_name='episode_season_relation')
    episode_id = models.CharField(verbose_name=_("Episode ID"), max_length=255, blank=True, null=True, editable=False)
    episode_sort = models.IntegerField(verbose_name=_("Episode Number"), blank=True, null=True, editable=False)
    synopsis = models.TextField(verbose_name=_("Synopsis"), blank=True, null=True, editable=False, max_length=255)
    date_added = models.DateTimeField(auto_now_add=True, blank=True, verbose_name=_("Date Added"))

CodePudding user response：

You can retrieve TvShows with:

from django.db.models import Max

TvShows.objects.alias(
    latest_episode_release=Max('episode_show_relation__release_date')
).order_by('-latest_episode_release')

or prior to django-3.2:

from django.db.models import Max

TvShows.objects.annotate(
    latest_episode_release=Max('episode_show_relation__release_date')
).order_by('-latest_episode_release')