在一张桌子上创建多对多 [英] Create many to many on one table

问题描述

Flask-SQLAlchemy提供了一个示例，其中介绍了如何创建多个许多关系.这是在两个不同的表之间完成的.

Flask-SQLAlchemy gives an example of how to create a many to many relationship. It is done between two different tables.

是否可以在同一张表上创建多对多关系?例如，一个姐姐可以有很多姐姐，而姐姐也可以有很多姐姐.我已经尝试过:

Is it possible to create a many to many relationship on the same table? For example a sister can have many sisters, who would also have many sisters. I have tried:

girl_sister_map = db.Table('girl_sister_map',
                      db.Column('girl_id', 
                                db.Integer, 
                                db.ForeignKey('girl.id')),
                      db.Column('sister_id', 
                                db.Integer, 
                                db.ForeignKey('girl.id')))

class Girl(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    sisters = db.relationship('Girl',
                              secondary=girl_sister_map,
                              backref=db.backref('othersisters', lazy='dynamic'))

但是当我尝试向一个女孩添加一个妹妹时，我得到了:

But when I try to add a sister to a girl I get:

sqlalchemy.exc.AmbiguousForeignKeysError:无法确定关系Girl.sisters的父/子表之间的联接条件-有多个外键路径通过辅助表"girl_sister_map"链接表.指定"foreign_keys"参数，提供这些列的列表，这些列应被视为包含从辅助表到每个父表和子表的外键引用.

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Girl.sisters - there are multiple foreign key paths linking the tables via secondary table 'girl_sister_map'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

这可能吗?我应该怎么做?

Is this possible? How should I be doing it?

推荐答案

您正在尝试构建所谓的

You are trying to build what is called an adjacency list. That is you have a table with foreign key to itself.

在您的特定情况下，它是

In your specific case it is a self referencial many to many relationship.

SQLAlchemy支持此功能，您可以通过上一个链接来发现.该文档包含几个示例.

This is supported in SQLAlchemy as you will discover by following the previous link. The doc contains several examples.

基本上，您将需要primaryjoin和secondaryjoin参数来建立您希望如何加入表的方式.直接来自文档:

Basically, you will need the primaryjoin and secondaryjoin arguments to establish how you would like to join the table. Straight from the doc:

Base = declarative_base()

node_to_node = Table("node_to_node", Base.metadata,
    Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
    Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
)

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    label = Column(String)
    right_nodes = relationship("Node",
                        secondary=node_to_node,
                        primaryjoin=id==node_to_node.c.left_node_id,
                        secondaryjoin=id==node_to_node.c.right_node_id,
                        backref="left_nodes"
    )

这篇关于在一张桌子上创建多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！