自引用关联关系SQLalchemy



在我使用flask sqlalchemy的flask应用程序中,我需要在两个联系人之间创建关联这是我的Contact型号

class Contact(db.Model):
    __tablename__ = 'contact'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(120), nullable=False, unique=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    to_contacts = db.relationship('Contact',
                                  secondary='ContactRelation',
                                  primaryjoin='id==contactrelation.c.from_contact_id',
                                  secondaryjoin='id==contactrelation.c.to_contact_id',
                                  backref='from_contacts')

和我的关联类ContactRelation:

class ContactRelation(db.Model):
    __tablename__ = 'contactrelation'
    id = db.Column(db.Integer, primary_key=True)
    from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
    to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
    relation_type = db.Column(db.String(100), nullable=True)

我有错误:

AttributeError: type object 'ContactRelation' has no attribute 'c'

感谢Michel和Simon在SQLAlchemy邮件列表中的帮助,我需要association_proxy和两个到Contact关系的关系。

class Contact(db.Model):
    __tablename__ = 'contact'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(120), nullable=False, unique=False)
    created_on = db.Column(db.DateTime, default=datetime.utcnow)
    birthday = db.Column(db.DateTime)
    background = db.Column(db.Text)
    photo = db.Column(db.Unicode(120))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    to_contacts = association_proxy('to_relations', 'to_contact')
    from_contacts = association_proxy('from_relations', 'from_contact')
class ContactRelation(db.Model):
    __tablename__ = 'contactrelation'
    id = db.Column(db.Integer, primary_key=True)
    from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
    to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
    relation_type = db.Column(db.String(100), nullable=True)
    from_contact = db.relationship(Contact,
                                   primaryjoin=(from_contact_id == Contact.id),
                                   backref='to_relations')
    to_contact = db.relationship(Contact,
                                 primaryjoin=(to_contact_id == Contact.id),
                                 backref='from_relations')

与关联对象的自引用多对多关系。

用户类别:

class User(Base):
    __tablename__ = "User"
    id = Column(String(36), primary_key=True, default=lambda : str(uuid1()))

关联类别:

class UserIgnore(Base):
    __tablename__ = "UserIgnore"
    id = Column(String(36), primary_key=True, default=lambda : str(uuid1()))
    ignored_by_id = Column("ignored_by_id", String(36), ForeignKey("User.id"), primary_key=True)
    ignored_by = relationship("User", backref="ignored_list",  primaryjoin=(User.id == ignored_by_id))
    ignored_id = Column("ignored_id", String(36), ForeignKey("User.id"), primary_key=True)
    ignored = relationship("User", backref="ignored_by_list",  primaryjoin=(User.id == ignored_id))

使用访问关系对象

someUser.ignored_list

someUser.ignored_by_list

感谢Sean

您的关系设计不正确。辅助表应该是一个普通表,而不是映射类。如果您想要ContactRelation上的额外数据(relation_type),您应该使用SQLAlchemy Relationship文档中描述的关联表模式:http://docs.sqlalchemy.org/en/rel_1_1/orm/basic_relationships.html#association-对象

如果您将to_contacts更改为以下内容,您的问题就会得到解决:

to_contacts = db.relationship('Contact',
                              secondary='ContactRelation',
                              primaryjoin='id==contactrelation.from_contact_id',
                              secondaryjoin='id==contactrelation.to_contact_id',
                              backref='from_contacts')

最新更新