CODEWITHSUNDEEP
pythonsqlalchemy
Niel
Niel

Reputation: 2006

SQLAlchemy InvalidRequestError when using composite foreign keys

My table relationships in SQLAlchemy have gotten quite complex, and now I'm stuck at this error no matter how I configure my relationship.

I'm a bit new to SQLAlchemy so I'm not sure what I'm doing wrong, but I keep getting the same error no matter what I do.

I have a hierarchy of tables, all inheriting from 'Node', which is a table with self-referential id and parent_id columns. All of this works. Now I have another Node table 'Information', which contains a composite primary key that is referenced by a different Node table, 'Widget'.

Base = declarative_base()

class Node(Base):

    __tablename__ = 'Node'
    id = Column(Integer, primary_key=True)
    parentid = Column(Integer, ForeignKey('Node.ID')
    type = Column(Text(50))

    children = relationship('Node')

    __mapper_args__ = {
        'polymorphic_identity': 'Node',
        'polymorphic_on': type
    } 

class Information(Node):

    __tablename__ = 'Information'
    id = Column(Integer, ForeignKey('Node.ID'), primary_key=True)
    Name = Column(Text, primary_key=True)
    Number = Column(Float, primary_key=True)

    Widgets = relationship('Widget', backref='ThisInformation')

    __mapper_args__ = {'polymorphic_identity': 'Information'}

class Widget(Node):

    __tablename__ = 'Widget'
    id = Column(Integer, ForeignKey('Node.ID'), primary_key=True)
    Name = Column(Text)
    UnitType = Column(Text)
    Amount = Column(Float)
    _Number = Column('Number', Float)

    __table_args__ = (ForeignKeyConstraint(
        ['Name', 'Number'],
        ['Information.Name', 'Information.Number']),
        {})

    __mapper_args__ = {'polymorphic_identity': 'Widget'}

I was worried this would give me circular reference issues, but instead it gives me this error:

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not determine join condition between parent/child tables on relationship Widget.Information - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I have tried adding foreign_keys arguments to the relationship, both on Widget and Information side, but I get the exact same error. Can anyone help?

Upvotes: 0

Views: 525

Answers (1)

Niel
Niel

Reputation: 2006

After a lot of searching I finally found a really simple answer in this answer to another question.

All I did was add 'inherit_condition': (id == Node.id) to the mapper in the classes that inherit from Node so that it looked like this:

__mapper_args__ = {'polymorphic_identity': 'Information',
                   'inherit_condition': (id == Node.id)}

And it works perfectly.

Upvotes: 0

Related Questions