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

Question

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.

This thread might be of interest - "Django annotating with a first element of a related queryset": https://stackoverflow.com/questions/23629431/django-annotating-with-a-first-element-of-a-related-queryset — whp, Jan 08 '23 at 23:42

Willem Van Onsem · Accepted Answer · 2023-01-08T23:45:36.953

1

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.

edited Jan 08 '23 at 23:45

answered Jan 08 '23 at 23:39

Willem Van Onsem

443,496
30
428
555

thanks for this great answer. One more question. How to get this for messages that their create_date is below certain date. `(MessageThread.objects.filter(Q(created_user=request.user)|Q(belonging_user=request.user),message__create_date__lte=before_date_time)).alias(last_message=Max('message__create_date')).order_by(F('last_message').desc(nulls_last=True))` doesnt work for me – Burak Er Jan 09 '23 at 01:05
@BurakEr: what do you mean with "below", is that "older" or "newer"? – Willem Van Onsem Jan 09 '23 at 09:23
I mean older ones. – Burak Er Jan 09 '23 at 12:19
@BurakEr: what is not working? – Willem Van Onsem Jan 09 '23 at 12:27
@WilliemVanOnsem I have solved the problem that was because my database inconsistency. – Burak Er Jan 09 '23 at 13:54

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

1 Answers1