CODEWITHSUNDEEP
pythondjangodjango-models
Matt
Matt

Reputation: 10392

Django queryset order by related model with distinct

I have the following two models:

class Show(models.Model):
    name = models.CharField(max_length=100)
    is_active = models.BooleanField(default=True)

class Episode(models.Model):
    start = models.DateTimeField()
    series = models.ForeignKey('Show', related_name='episodes')

I want to retrieve all instances of Show which have is_active set to True and which have at least 1 Episode. The part I'm having trouble with is that I also want the Show instances to be ordered by the related Episode start field and for the Show instances to be unique.

Is this possible with a single query?

What I have tried:

Show.objects.annotate(
    e_count=Count('episodes')
).filter(
    e_count__gt=0,
    is_active=True,
).order_by('episodes__start').distinct()

Upvotes: 1

Views: 575

Answers (1)

Matt
Matt

Reputation: 10392

I achieved this by using Min, as suggested by Anonymous.

Show.objects.filter(
    is_active=True,
).annotate(
    e_count=Count('episodes'),
    start=Min('episodes__start'),
).filter(
    e_count__gt=0,
    start__gt=timezone.now(),
).order_by('start')

Upvotes: 2

Related Questions