Reputation: 15
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
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