SQLALCHEMY - 许多多对多的一元关系

I have a table called Node. Each node has many children of type Node. Also, a Node object has many parents. I also made an algorithm to find siblings (node with similar parents)

我有一个名为Node的表。每个节点都有许多Node类型的子节点。此外，Node对象有许多父对象。我还做了一个算法来找到兄弟姐妹（父母相似的节点）

how to make it? do I make a separate table for them? or do I make it in the same table? here is how I tried to do it and failed obviously:

怎么做？为他们单独制作一张桌子？还是我在同一张桌子上做？这是我试图做到的方式，但显然失败了：

class Node(Model):
    id = Column(String, primary_key=True)
    name = Column(String)
    parent_id = db.Column(db.String, db.ForeignKey('node.id'))

    children = db.relationship('node', remote_side=[id], uselist=True)
    parents = db.relationship('node', remote_side=[id], uselist=True)
    siblings = db.relationship('node', remote_side=[id], uselist=True)

I have no idea how to make this happen.

我不知道如何实现这一目标。

I actually thought about using a graphDB for this node object. And the other tables with classic SQL but I am not sure it is worth the fuss

我实际上考虑过为此节点对象使用graphDB。而其他表与经典SQL但我不确定这是值得的大惊小怪

1 个解决方案

#1

Although I don't know how to implement "siblings" yet, here is how to have many self-referential many-to-many relationships:

虽然我不知道如何实现“兄弟姐妹”，但这里是如何有许多自我引用的多对多关系：

Connection = Table('connection',
    Column('child_id', String, ForeignKey('node.id')),
    Column('parent_id', String, ForeignKey('node.id')),
    UniqueConstraint('parent_id', 'child_id', name='unique_usage')
)


class Node(Model):
    id = Column(String, primary_key=True)
    name = Column(String)

    # this is the result list of type Node 
    # where the current node is the "other party" or "child"
    parents = relationship('Node', secondary=Connection, 
                            primaryjoin=id == Connection.c.parent_id,
                            secondaryjoin=id == Connection.c.child_id)

    # this is the result list of type Node 
    # where the current node is the "parent" 
    children = relationship('Node', secondary=Connection, 
                            primaryjoin=id == Connection.c.child_id,
                            secondaryjoin=id == Connection.c.parent_id)

basically, for each wanted many-to-many relationship, make the table representing the relationship, then add the relation to your module. You can have two-way relations for each one of them

基本上，对于每个想要的多对多关系，使表格表示关系，然后将关系添加到您的模块。你可以为每一个都有双向关系

I will edit my answer later when I figure how to make siblings

当我想出如何制作兄弟姐妹时，我会稍后编辑我的答案

#1