Create Many To Many On One Table

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

Solution 1:

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.

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

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"
    )