Django - Ordering by a foreign key

Question

I have a class that's called Movie:

class Movie(models.Model):
    title = models.CharField(max_length=511)
    tmdb_id = models.IntegerField(null=True, blank=True, unique=True)
    release = models.DateField(null=True, blank=True)
    poster = models.TextField(max_length=500, null=True)
    backdrop = models.TextField(max_length=500, null=True, blank=True)
    popularity = models.TextField(null=True, blank=True)

and a class named Trailer:

class Trailer(models.Model):
    movie = models.ForeignKey(Movie, on_delete=models.CASCADE)
    link = models.CharField(max_length=100)
    date = models.DateTimeField(auto_now_add=True)

How can I display all movies ordered by the date of the trailer?

I have tried: Movie.objects.order_by('trailer__date') but that causes multiple duplicates and doesn't show them on the right order either, how can I avoid the duplicates and have one entry per each movies ordered by the date of the Trailer object?

Edit: I just noticed that it doesn't display all entries but just some of them

Answer 1

Update: the OP wanted this sorted by the latest trailer date, and not by the earliest trailer date.

You can use annotate here if you want:

from django.db.models import Max
qs = Movie.objects.values('title').annotate(latest_trailer=Max('trailer__date')).order_by('-latest_trailer')
# Add other columns you want into the `values` if you need them

Or you can use your original query and couple it with a dict.

from collections import OrderedDict
qs = Movie.objects.order_by('-trailer__date')
movies = OrderedDict()
for x in qs:
    if x.id not in movies:
        movies[x.id] = x
movies = movies.values()

It was unclear what order you wanted the movies in when they had multiple trailers, so I guessed it was based on the earliest trailer.