Django Sort by backward foreign key

Question

I currently have the following models

class ChatRoom(models.Model):
      creator = models.ForeignKey('User') # points to the initial user

class Message(models.Model):
      room = models.ForeignKey('ChatRoom')
      text = models.CharField(max_length=500)
      date = models.DateTimeField()
      from = models.ForeignKey('User') # points to some user

For any given user who logs into my website, I want to display a list of the chat rooms they have created, ordered by the date of their last message (latest activity) along with the last message entered to the chatroom

So something like

Your chat rooms
---------------
science - "hey guys, anyone know how to do calculus?" 5m
art     - "hey guys, I like art" 10m

How would I perform this query in django? Keep in mind that a person might have many, many, many chat rooms and thus we can't just load all the chat_rooms with ChatRoom.objects.all() and manually iterate through it.

Answer 1

You need to have a additional relation to your last message, otherwise you will not be able to order by them.

class ChatRoom(models.Model):
      creator = models.ForeignKey('User') # points to the initial user
      # you will need to set this in view or with signals.(don't forget to handle delete)
      last_message = models.ForignKey('Message')

And that you can do this

ChatRoom.objects.filter(...).order_by('last_message__date')