How to sort distinct values with respect to manytoone class in Django?

January 9, 2023

I have two models

class MessageThread(models.Model):
    title = models.CharField(max_length=120, blank=True, null=True)
    created_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name='created_user')
    belonging_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    last_message_date = models.DateTimeField(blank=True, null=True)

and

class Message(models.Model):
    thread = models.ForeignKey(MessageThread, on_delete=models.SET_NULL, null=True)
    user = models.ForeignKey(User, null=True, blank=True, on_delete=models.SET_NULL)
    comments = models.TextField(blank=True, null=True, max_length=500)
    create_date = models.DateTimeField(blank=True, null=True)

Here, I want to get MessageThreads that are sorted by their last Messages’ create_date.

I tried to get sorted Messages by ‘-create_date’ and then get distinct thread ids from that query but it doesnt work. I am using PostgreSQL.

>Solution :

You can order by the maximum of the message__created_date, so:

from django.db.models import F, Max

MessageThread.objects.alias(
    last_message=Max('message__create_date')
).order_by(F('last_message').desc(nulls_last=True))

You should not have a field last_message_date in the MessageThread model: you can determine this dynamically, when needed.

Note: Django’s DateTimeField ^[Django-doc]
has a auto_now_add=… parameter ^[Django-doc]
to work with timestamps. This will automatically assign the current datetime
when creating the object, and mark it as non-editable (editable=False), such
that it does not appear in ModelForms by default.

Note: Ordering with NULLs depends on the database backend. You can work with .desc(nulls_last=True) ^[Django-doc] to ensure that the NULLs will be put last when ordering in descending order.