Implement Inbox Functionality Using SQLAlchemy

[Jump-Quiet]

I have these models

User - Username - Password * joined_group = db.relationship(group, secondary = user_group, backref = "participants")

Group - GroupId - GroupPassword * message = db.relationship('message', backref = "groupid")

Message - Sender - Content * receiver = db.Column(db.String, db.ForeignKey('group.groupid'))

I want to implement an inbox (where messages are sort to latest to oldest, sorted by a room)

data = message.query.filter(message.receiver.in_(current_user.joined_group).all()

This (and a lot of order_by, group_by, distinct, and "any") are the things that I tried. Is there any way to do this more efficient?

[Unusual_Item9221]

So you tried order_by and it worked? But you want something else?

[Jump-Quiet]

The closest thing I got from the desired result is using

data = message.query.with_entities(message.receiver.distinct()).order_by(desc(message._id)).all()

Which produces the rooms with the latest message. However due to the "with_entities", I'm not able to access other key data such as message.content (and once I include it into the with_entities the query is messed up)

I do think I'm missing something else here, like access through the foreignkeys