Queryset API distinct() does not work?

class Message(models.Model):
    subject = models.CharField(max_length=100)
    pub_date = models.DateTimeField(default=datetime.now())

class Topic(models.Model):
    title = models.CharField(max_length=100)
    message = models.ManyToManyField(Message, verbose_name='Discussion')

I want to get order all the topics according to the latest message object attached to that topic. I executed this query but this does not give the distinct queryset.

>> Topic.objects.order_by('-message__pub_date').distinct()

From stackoverflow

• What is your query? Try <QuerySet>.query.as_sql() to see raw SQL

• You'll find the explanation in the documentation for .distinct().

  I would de-normalize by adding a modified_date field to the Topic model and updating it whenever a Message is saved or deleted.

• You don't need distinct() here, what you need is aggregation. This query will do what you want:

  from django.db.models import Max
  Topic.objects.annotate(Max('message__pub_date')).order_by('-message__pub_date__max')
  

  Though if this is production code, you'll probably want to follow akaihola's advice and denormalize "last_message_posted" onto the Topic model directly.

  Also, there's an error in your default value for Message.pub_date. As you have it now, whenever you first run the server and this code is loaded, datetime.now() will be executed once and that value will be used as the pub_date for all Messages. Use this instead to pass the callable itself so it isn't called until each Message is created:

  pub_date = models.DateTimeField(default=datetime.now)