CODEWITHSUNDEEP
pythonpython-3.xsqlalchemyflask-sqlalchemy
beyondfloatingpoint
beyondfloatingpoint

Reputation: 1279

Proper referencing between inherited classes in SQLAlchemy

Let's say we have some template and we later wish to create multiple objects, based on this template, preserving link to this object and also having backward link to call the objects created based on this template, so we would implement classes like this:

class BasicSentence(db.Model):
    __tablename__ = 'basic_sentences'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(25))
    type = db.Column(db.String(50))         
    __mapper_args__ = {
        'polymorphic_identity': 'basic_sentence',
        'polymorphic_on': type
    }

sentence_layout.py:

class SentenceLayout(BasicSentence, db.Model):
    __tablename__ = 'sentences_layouts'

    id = db.Column(db.Integer, db.ForeignKey('basic_sentences.id'), primary_key=True)

    # RELATIONSHIP
    sentenences = relationship("Sentence",
                                  back_populates="sentence_layout")

    __mapper_args__ = {
        'polymorphic_identity': 'sentence_layout',
        'inherit_condition': id == BasicSentence.id
    }

sentence.py:

class Sentence(BasicSentence, db.Model):
    __tablename__ = 'advertisements'

    id = db.Column(db.Integer, db.ForeignKey('basic_sentences.id'), primary_key=True)

    # Relationships
    sentence_layout_id = db.Column(db.Integer, db.ForeignKey('sentences_layouts.id'))
    sentence_layout = relationship(SentenceLayout, foreign_keys=[sentence_layout_id],
                                        back_populates="advertisements")

    __mapper_args__ = {
        'polymorphic_identity': 'sentence',
        'inherit_condition': id == BasicSentence.id,
    }

Problem is that this results in:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'Join object on basic_sentences(4379708104) and sentences_layouts(4379795752)' and 'Join object on basic_sentences(4379708104) and sentences(4379797488)'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

During handling of the above exception, another exception occurred:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship SentenceLayout.sentences - 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.

Python 3.6, SQLAlchemy 1.2.2

So what is proper referencing between inherited classes in SQLAlchemy?

Upvotes: 2

Views: 384

Answers (1)

univerio
univerio

Reputation: 20508

You need to specify the foreign_keys argument on SentenceLayout.sentenences as well, not just the Sentence.sentence_layout relationship:

sentenences = relationship("Sentence", foreign_keys=Sentence.sentence_layout_id,
                           back_populates="sentence_layout")

Because of circularity, you may need to use a lambda or a string instead:

sentenences = relationship("Sentence", foreign_keys=lambda: full.reference.to.sentence.Sentence.sentence_layout_id,
                           back_populates="sentence_layout")

or

sentenences = relationship("Sentence", foreign_keys="Sentence.sentence_layout_id",
                           back_populates="sentence_layout")

Upvotes: 1

Related Questions