Flask-AppBuilder Many-to-Many relationship within the same Table

Question

I have many to many relationships between the same table and want to define them in Flask-AppBuilder. For example, a parent-child relationship between humans, where both instances belong to the same table "Human". However when I do this, I get the error:

Error

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Human.child - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Example models.py

from flask_appbuilder import Model
from sqlalchemy import Column, String, ForeignKey
from sqlalchemy.orm import relationship

class HumanHasChild(Model):
    parent_id = Column(String(200), ForeignKey("Human.id"), primary_key=True)
    child_id = Column(String(200), ForeignKey("Human.id"), primary_key=True)

class Human(Model):
    id = Column(String(200), primary_key=True)
    child = relationship("HumanHasChild", foreign_keys="HumanHasChild.child_id")

Contrary to the error message, I do specify the foreign key three times. However I tried adding an explicit join condition as well:

class Human(Model):
    id = Column(String(200), primary_key=True)
    child = relationship("HumanHasChild", foreign_keys="HumanHasChild.child_id",primaryjoin="Human.id==HumanHasChild.parent_id")         

However even then I get an error:

sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'human.id = human_has_child.parent_id' on relationship Human.child. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.

How can I get many-to-many relationships within the same table to work?

Answer 1

Following SQLAlchemy Many-to-Many Relationship on a Single Table, the answer is:

from flask_appbuilder import Model
from sqlalchemy import Column, String, ForeignKey, Table
from sqlalchemy.orm import relationship

HumanHasChild = Table("HumanHashChild",Model.metadata,
    Column("parent_id",String(200), ForeignKey("Human.id"), primary_key=True),
    Column("child_id",String(200), ForeignKey("Human.id"), primary_key=True)                                                                                                                                                                                                                                                  
    )

class Human(Model):
    id = Column(String(200), primary_key=True)
    child = relationship("Human",
    secondary=HumanHasChild,
    foreign_keys = [HumanHasChild.c.parent_id,HumanHasChild.c.child_id],
    primaryjoin=id==HumanHasChild.c.parent_id,
    secondaryjoin=id==HumanHasChild.c.child_id,
    backref="children")