原题

class MessageLog(Base):
    __tablename__ = 'messagelogs'
    id          = Column(Integer, primary_key=True)
    receiver_id = Column(Integer, ForeignKey('users.id'))
    message_id  = Column(Integer, ForeignKey('messages.id'), nullable=True)
    messagelog_status = Column('messagelog_status', Enum('unread', 'read', 'delete'), nullable=False)
    message     = relationship("Message", backref="receivers")
    receiver    = relationship("User", backref=backref("receive_messages", cascade="all, delete, delete-orphan"))

class Message(Base):
    __tablename__ = 'messages'
    id        = Column(Integer, primary_key=True)
    sender_id = Column(Integer, ForeignKey('users.id'))
    body      = Column(String(256), nullable=False)
    type      = Column('message_types', Enum('private', 'public', 'global'), nullable=False)
    posted_at = Column(DateTime, default=func.current_timestamp())
    message_status = Column('message_status', Enum('draft', 'sent', 'deleted'), nullable=False)
    sender    = relationship("User", backref="sent_messages")

以上是站内信相关的模型,基于这篇文章

http://www.cnblogs.com/hejiaquan/archive/2012/04/07/2435817.html

用户登录,查询未读信件,推送消息。

系统消息只插入Message表, type=global。 查询的时候,保存在Message表里但又不在MeesageLog里面的message记录是系统站内信。

如何一次查询,即可得到(MessageLog.receiver_id=logged_in.id) 和 系统消息里面的所有未读记录?

  • 追加问题:两个人的来往私信, 如何查询

    session.query(MessageLog, Message).filter(.message_id == ..id)

    收件人=我,发件人=对方 || 收件人=对方,发件人=我

  • 追加问题2:如何在Sqlalchemy里面查询的时候,动态地插入一条数据??

  • 追加问题3:如何查询我有多少联系人?(给我发信的人 + 接收我信件的人)

解答

尝试回答一下。

我觉得这样的设计,把问题复杂化了。为什么不只做一张表呢?Message这张表,只有 sender_id,没有 receiver_id。用户 A 给用户 B 发消息,程序要操作两张表(message 和 messagelog)?好奇怪。

我认为更好的设计是只有一张表,包含了 sender_id、reciever_id、message_status、messagelog_status 等;

class Message(Base):
    __tablename__ = 'messages'
    id        = Column(Integer, primary_key=True)   
    sender_id = Column(Integer, ForeignKey('users.id'))
    receiver_id = Column(Integer, ForeignKey('users.id')) # 新增
    body      = Column(String(256), nullable=False)
    type      = Column('message_types', Enum('private', 'public', 'global'), nullable=False)
    posted_at = Column(DateTime, default=func.current_timestamp())
    message_status = Column('message_status', Enum('draft', 'sent', 'deleted'), nullable=False)
    messagelog_status = Column('messagelog_status', Enum('unread', 'read', 'delete'), nullable=False) # 新增

So,问题解析和解答:

如何一次查询,即可得到(MessageLog.receiver_id=logged_in.id) 和 系统消息里面的所有未读记录?

也即 得到当前登录用户(loggined_id)应该收到的两类消息:系统消息、指定发给 TA 的。

import sqlalchemy as sa

messages = session.query(Message).filter(
    sa.or_(
        Message.receiver_id == logged_in.id,
        sa.and_(Message.type=='global',Message.messagelog_status=='unread')
    )
)

两个人的来往私信

import sqlalchemy as sa

user_id_a = 1
user_id_b = 2

messages = session.query(Message).filter(
    sa.or_(
        sa.and_(Message.sender_id==user_id_a,Message.receiver_id=user_id_b),
        sa.and_(Message.sender_id==user_id_b,Message.receiver_id=user_id_a)
    )
)

如何在Sqlalchemy里面查询的时候,动态地插入一条数据?

交给接口去处理。callback 什么的。

另外,不建议在 sender_id 和 receiver_id 处使用 ForeignKey,就直接 Integer 好了。