Sqlalchemy 线程消息系统

Question

我刚接触sqlalchemy，现在在用Flask开发网站。我想做一个消息系统，类似于Facebook的对话功能。我有以下模型，这段代码可以正常工作：

message_read_state_relations = db.Table('message_message_read_state_relations',
        db.Column('user_id', db.Integer, db.ForeignKey('auth_users.id')),
        db.Column('message_id', db.Integer, db.ForeignKey('messages.id')),
)

class User(db.Model):
        __tablename__ = 'auth_users'

        id = db.Column(db.Integer, primary_key=True)
        email = db.Column(db.String(100))
        password = db.Column(db.String(100))

        messages = db.relationship('Message', backref=db.backref('user'), lazy='dynamic')

        threads = association_proxy('user_thread_relations', 'thread')


class Message(db.Model):
        __tablename__ = 'messages'

        id = db.Column(db.Integer, primary_key=True)
        text = db.Column(db.Text, default='')

        thread_id = db.Column(db.Integer, db.ForeignKey('threads.id'))
        user_id = db.Column(db.Integer, db.ForeignKey('auth_users.id'))

        user_read_states = db.relationship('User', secondary=message_read_state_relations,
                backref=db.backref('message_read_states'), lazy='joined')


class Thread(db.Model):
        __tablename__ = 'threads'

        id = db.Column(db.Integer, primary_key=True)
        subject = db.Column(db.String(255))

        messages = db.relationship('Message', backref=db.backref('thread'), lazy='dynamic')
        users = association_proxy('user_thread_relations', 'user')


class UserThreadRelation(db.Model):
        __tablename__ = 'user_thread_relations'

        user_id = db.Column(db.Integer, db.ForeignKey('auth_users.id'), primary_key=True)
        thread_id = db.Column(db.Integer, db.ForeignKey('threads.id'), primary_key=True)

        is_deleted = db.Column(db.Boolean, default=False)

        user = db.relationship(User, backref=db.backref('user_thread_relations'))
        thread = db.relationship(Thread, backref=db.backref('user_thread_relations'))

但是我有些地方不太明白。例如，我怎么才能获取某个用户的所有未读消息的数量？或者在某个线程中该用户的未读消息数量呢？

Message和User之间通过message_read_state_relations建立了关系。我该如何获取属于所有线程的所有消息，而不考虑这个用户的message_read_state_relations呢？

或者，也许我做的方向都错了？这个设计是不是有问题？我是根据这个问题创建的 线程消息系统数据库架构设计

谢谢任何帮助！

Answer 1

我也在尝试用Flask和SQLAlchemy开发一个线程消息系统。在搜索的时候，我发现了你的问题，还有一个关于Django的GitHub库（链接在下面）。我觉得这个模型可能会很有用。如果你在6年后还感兴趣，可以给我留言。

https://github.com/philippWassibauer/django-threaded-messages/blob/master/threaded_messages/models.py

Answer 2

我不确定这是否是最好的设计，但看起来还不错。关于你问的那些查询，可以通过连接（joins）和子查询（subqueries）来构建，你可以去看看SQLAlchemy的文档，了解这些内容。我还建议你先熟悉一下纯SQL的查询方式，因为SQLAlchemy在很大程度上是跟SQL相似的，理解SQL会大大帮助你写出更好的SQLAlchemy查询。这里有一个查询的例子（获取用户所有未读消息的数量）：

# First count all the messages.
# On a big set of data this may be very slow operation.
total_msgs = db.session.query(db.func.count(Message.id).label('cnt')).subquery()
# Then count messages read by the user.
read_msgs = db.session.query(db.func.count(Message.id).label('cnt')).\
    join(User.message_read_states).filter(User.id == 1).subquery()
# Subtract read messages count from total count.
unread_count = db.session.query(total_msgs.c.cnt - read_msgs.c.cnt).scalar()