SQLAlchemy使用关联配置与自身的多对多关系 [英] SQLAlchemy configuring many-to-many relationship to self using Association

问题描述

我在为模型本身配置多对多关系时遇到问题.使用

I am having problems configuring a many to many relationship to a model itself. I can configure a self-many-to-many relation when I use anormal relationship configuration i.e. one not using an Association object.

在这种情况下，我必须在多对多表本身中记录一些额外的信息，因此我试图使用关联对象(PageLink)来实现关系.

In this scenario I have to record some extra information in the many-to-many table itself so I'm trying to implement relationship using an Association object (PageLink).

这里是模型.

class PageLink(Base):
    '''
    Association table.
    '''
    __tablename__ = 'page_links'

    id = Column(Integer,primary_key=True)
    page_from = Column(Integer,ForeignKey('page.id'),primary_key=True)
    page_to = Column(Integer,ForeignKey('page.id'),primary_key=True)
    extra_col1 = Column(String(256),nullable=False)

class Page(Base):
    '''
    main table
    '''

    __tablename__ = 'page'

    id = Column(Integer,primary_key=True)
    name = Column(String(56),nullable=False)

    linked = relationship('PageLinks',backref='parent_page',
                          primaryjoin=id==PageLink.page_from,
                          secondaryjoin=id==PageLink.page_to)

这种方法行不通.我曾尝试删除'secondaryjoin'关键字，但无法正常工作.

This approach does not work. I have tried removing 'secondaryjoin' keyword but it wouldn't work.

非常感谢您对此事的任何帮助或建议.

Would greatly appreciate any help or advice on this matter.

感谢您的阅读.

推荐答案

关联对象模式不是多对多关系的特殊化，而是一对多关系的特例，其中您拥有一个left_table-多对一-association_table-一对多-right_table设置.简而言之，您需要两个 关系，两个关系都不应该具有secondary/secondaryjoin.

The association object pattern is not a sepecialization of the many-to-many relationship, but rather a special case of one-to-many relationships where you have a left_table - Many-To-One - association_table - One-To-Many - right_table set up. In short, you need two relationships, neither of which should have a secondary/secondaryjoin.

class PageLink(Base):
    '''
    Association table.
    '''
    __tablename__ = 'page_links'

    id = Column(Integer,primary_key=True)
    page_from = Column(Integer,ForeignKey('page.id'),primary_key=True)
    page_to = Column(Integer,ForeignKey('page.id'),primary_key=True)
    extra_col1 = Column(String(256),nullable=False)

class Page(Base):
    '''
    main table
    '''

    __tablename__ = 'page'

    id = Column(Integer,primary_key=True)
    name = Column(String(56),nullable=False)

    linked_to = relationship('PageLinks',backref='parent_page',
                             primaryjoin=id==PageLink.page_from)
    linked_from = relationship('PageLinks',backref='child_page',
                               primaryjoin=id==PageLink.page_to)

这意味着，要访问某个页面p中收件人"链接的额外列，您必须执行:p.linked_to[0].extra_col1，或获取实际的链接页面，p.linked_to[0].page_to

which means, to access the extra column for the 'to' links from some page p, you have to do: p.linked_to[0].extra_col1, or to get the actual linked page, p.linked_to[0].page_to

顺便说一句，使用自动增量主键或(左/右)外键对作为关联中的主键通常是一个好主意，但是同时使用这两个主键几乎没有用.结合这两种想法的另一种方法是将自动增量整数用作主键中的唯一列，并对左/右外键列具有附加的唯一约束.

As an aside, it's often a great idea to use either an autoincrement primary key or (left/right) foreign key pair as the primary key in associations, but almost never useful to have both in the primary key. An alternative that combines both ideas would be to use an autoincrement integer as the only column in the primary key, and have an additional unique constraint on the left/right foreign key columns.

这篇关于SQLAlchemy使用关联配置与自身的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！