python - Cannot define inter dependent relationships among models using SQLAlchemy

Question

In following code examples ChatMessage model has required field converstaion_id which defines the relationship with ChatConversation of that model. ChatConverstation model has a field last_message_id which is the reference to the last ChatMessage.

I am getting following error message from sqlalchemy and do not know how to resolve this issue. Please help.

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class ChatConversation->chat_conversation, expression 'ChatMessage' failed to locate a name ('ChatMessage'). If this is a class name, consider adding this relationship() to the <class 'chat.models.conversation.ChatConversation'> class after both dependent classes have been defined.

How should I define schema for these models so that I can set both relationships (ie. conversation relationship in ChatMessage model and last_message relationship in ChatConversation model) ?

class ChatConversation(db.Model):
  __tablename__ = 'chat_conversation'

  id = db.Column(db.Integer, primary_key=True)
  last_message_id = db.Column(
      db.Integer,
      db.ForeignKey("chat_message.id", ondelete='SET NULL'),
      nullable = True
  )
  last_message = db.relationship('ChatMessage')

class ChatMessage(db.Model):
  __tablename__ = 'chat_message'

  id = db.Column(db.Integer, primary_key=True)
  conversation_id = db.Column(
      db.Integer,
      db.ForeignKey("chat_conversation.id", ondelete='CASCADE'),
      nullable=False
  )
  conversation = db.relationship('ChatConversation')

标签： pythonsqlalchemyflask-sqlalchemy

Answer 1

解决方案是使用hybrid_property方法装饰器sqlalchemy并foreign_keys显式定义来解决ForeignKey.

from sqlalchemy.ext.hybrid import hybrid_property

class ChatConversation(db.Model):
  __tablename__ = 'chat_conversation'

  id = db.Column(db.Integer, primary_key=True)
  last_message_id = db.Column(
      db.Integer,
      db.ForeignKey("chat_message.id", ondelete='SET NULL'),
      nullable = True
  )
  
  @hybrid_property
  def last_message(self):
    return False if not self.last_message_id else ChatMessage.query.get(self.last_message_id)

class ChatMessage(db.Model):
  __tablename__ = 'chat_message'

  id = db.Column(db.Integer, primary_key=True)
  conversation_id = db.Column(
      db.Integer,
      db.ForeignKey("chat_conversation.id", ondelete='CASCADE'),
      nullable=False
  )
  conversation = db.relationship('ChatConversation', foreign_keys = [conversation_id])