Queryset API distinct() does not work?

Question

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()

Answer 1

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

Answer 2

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.

Answer 3

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)