Django Chatroom model: Retrieve latest messages from all rooms a user is part of

I'm having a hard time constructing a query (sqlite) to get the latest message for each chatroom a user is part of. This is my model in Django:

class Membership(models.Model):
    room_name = models.TextField()
    members = models.ManyToManyField(CustomUser, through="Message")

class Message(models.Model):
    user = models.ForeignKey(CustomUser, on_delete=models.CASCADE)
    room = models.ForeignKey(Membership, on_delete=models.CASCADE)
    advertisement = models.ForeignKey(Advertisement, on_delete=models.CASCADE, default=None)
    message = models.TextField()
    created_at = models.DateTimeField(auto_now_add=True)

class CustomUser(AbstractUser):
    username = models.CharField(max_length=200, unique=False)

Now with this in mind I would like to display all rooms a user is part of and additionally the latest message in a room. I feel like I'm close:

select *
from chat_message as cm
inner join (select max(id) as lm from chat_message group by room_id) as last_messages on last_messages.lm = cm.id
inner join main_advertisement ma on cm.advertisement_id = ma.id
inner join chat_membership as cmb ON cm.room_id = cmb.id
order by id;

Which looks like this gives me the latest messages (and other data) for each room but now I struggle to filter for just 1 user (e.g. user_id = 3).

Any ideas would be appreciated, also for possible hints how to start constructing this in the django ORM.

You can add a filter to your inner query where you get the maximum chat_message ids:

select *
from chat_message as cm
inner join (select max(id) as lm from chat_message where user_id = 3 group by room_id) as last_messages on last_messages.lm = cm.id
inner join main_advertisement ma on cm.advertisement_id = ma.id
inner join chat_membership as cmb ON cm.room_id = cmb.id
order by id;
Back to Top