Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I for the life of me cannot figure out why this self-referential many-to-many will not be happy:

minor_contains = db.Table(
    'minor_contains',
    db.Column('parent_id', db.Integer, db.ForeignKey('minors.id'),
            primary_key=True),
    db.Column('contains_id', db.Integer, db.ForeignKey('minors.id'),
            primary_key=True))
class Minor(db.Model):
    __tablename__ = 'minors'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    contains = db.relationship(
        "Minor",
        secondary=minor_contains,
        primaryjoin="id == minor_contains.c.parent_id",
        secondaryjoin="id == minor_contains.c.contains_id",
        backref="contained_by",
        lazy='dynamic')
I've tried reworking it a few different ways based on examples I've seen for SQLAlchemy and for Flask-SQLAlchemy, but I consistently end up where either I get the following error message or I end up in an infinite loop somewhere.
E           ArgumentError: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'minor_contains.parent_id = :parent_id_1' on relationship Minor.contains.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True.
UPDATE
I'm really failing to understand the error message because it shows the column from the join table being compared to itself, where the condition on the join should be the PK of the minors table compared to the FK in the join table.
I'll also add a version of this that just hangs forever.  You'll see I've been reworking variable names and such just rewriting it over and over hoping if I take a fresh stab at it, I'll somehow be smarter the second or fifth time around.
minor_contains = db.Table(
    'minor_contains',
    db.Column('parent_minor_id', db.Integer, db.ForeignKey('minors.id'),
            primary_key=True),
    db.Column('contains_minor_id', db.Integer, db.ForeignKey('minors.id'),
            primary_key=True))
class Minor(db.Model):
    __tablename__ = 'minors'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    contains = db.relationship(
        "Minor",
        secondary=minor_contains,
        primaryjoin=id==minor_contains.c.parent_minor_id,
        secondaryjoin=id==minor_contains.c.contains_minor_id,
        backref=db.backref("minor_contains", lazy='dynamic'))
I think it's necessary to specify the model name in the join conditions.
contains = db.relationship(
    "Minor",
    secondary=minor_contains,
    primaryjoin="Minor.id == minor_contains.c.parent_id",
    secondaryjoin="Minor.id == minor_contains.c.contains_id",
    backref=db.backref('minor_contains', lazy='dynamic'),
    lazy='dynamic')
I had the same issue and it fixed the problem. I found a useful answer here :
                Hyperlink was super helpful... particularly - "When defining a many-to-many self-referential relationship, I found that the primary key column has to be defined in the table itself. It cannot be defined in the model base or a mixin class from which this entity is derived." This alone solved my problem.
– Krejko
                May 6, 2017 at 22:54
minor_contains = db.Table(
    'minor_contains',
    db.Column('parent_id', db.Integer, db.ForeignKey('minors.id'),
        primary_key=True),
    db.Column('contains_id', db.Integer, db.ForeignKey('minors.id'),
        primary_key=True))
class Minor(db.Model):
    __tablename__ = 'minors'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    contains = db.relationship(
        "Minor",
        secondary=minor_contains,
        primaryjoin="id == minor_contains.c.parent_id",
        secondaryjoin="id == minor_contains.c.contains_id",
        backref=db.backref('minor_contains', lazy='dynamic'),
        lazy='dynamic')
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.