MySalick
MySalick

Reputation: 15

How to sort by field using ForeignKey

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)

Upvotes: 1

Views: 33

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

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

Upvotes: 1

Related Questions