How to sort by field using ForeignKey

Question

I have a queryset of all objects in the Room model. qs = Rooms.objects.all() I need to sort it by the last received message in the Message table. There I have a variable room, which has a FK relation to Room. I.e. the last created Message object, should act as 1 Room, the penultimate should go second, etc. How can I in Room refer to timestamp for sorting? qs.order_by("message_room__timestamp") I tried to do qs.order_by("message_room__timestamp"), but for some reason I get a very strange response, I get duplicate rooms

class Room(models.Model):
    name = models.CharField(max_length=255, unique=True)


class Message(models.Model):
    room = models.ForeignKey(Room, on_delete=models.CASCADE, default=None, null=True, related_name="message_room")
    text = models.CharField(max_length=255)
    timestamp = models.DateTimeField(auto_now_add=True)

Answer 1

The reason this happens is because it will make a LEFT OUTER JOIN on Message, and thus for each Message, return the Room once, and thus the same room can occur multiple times.

You can annotate with the largest timestamp, so:

from django.db.models import Max

Room.objects.alias(
    latest_message=Max('message_room__timestamp')
).order_by('latest_message')