sqlalchemy nested inheritance / polymorphic relationships

Question

I have a tree that looks like this, reflected via polymorphic inheritance:

      A
  /   |   \
  B   C   D

That works great, like:

class BaseModel(db.Model):     # Table A in diagram
    __tablename__ = "entities"

    id = db.Column(db.BigInteger, primary_key=True, nullable=False, server_default=func.nextval('guid_seq'))
    type_id = db.Column(db.SmallInteger, db.ForeignKey(EntityTypesModel.id))

    __mapper_args__ = {
        'polymorphic_identity':'entity',
        'polymorphic_on':type_id,
        'with_polymorphic':'*'
    }

class BrandModel(BaseModel):   # Table B, C, D in diagram
    __tablename__ = 'brands'

    id = db.Column(db.BigInteger, db.ForeignKey(StufffModel.id), primary_key=True, nullable=False)
    name = db.Column(db.String, nullable=False)

    __mapper_args__ = {
        'polymorphic_identity':ET_BRAND,
    }

The problem is I need to reflect something more like this:

Where D is not only a polymorphic child of A but also the polymorphic parents of E & F.

It seems like I have to choose, D can either be a polymorphic child or it can be a parent - it can't be both.

Do I have any options here?

EDIT:

Just to tie this off, I ended up flattening the tree so it looks like:

      A
  /   |   \   \
 B    C    E   F

D is now gone and the functionality it provided is in the children (E & F). I'll probably make the common parts a mixin or something.

Unfortunate but I couldn't spend more time on this particular issue.

van · Accepted Answer · 2013-06-04 07:55:24Z

5

You definitely can do this. The code below is using the declarative_base, but shows the model setup which works. D class is both a parent and a child using class inheritance. However, the polymorphic_identity is stored only on the top level. Make sure you have all proper foreign keys and class inheritances set up.

*Note: you example defines type_id as Numeric, however the values seem to be Strings.*

Base = declarative_base(cls=_BaseMixin)
Base.query = session.query_property()

class BaseModel(Base):
    __tablename__ = 'entities'
    id = Column(Integer, primary_key=True)
    #type_id = Column(Integer, nullable=False)
    type_id = Column(String, nullable=False)
    __mapper_args__ = {
        'polymorphic_identity': 'entity',
        'polymorphic_on':type_id,
        'with_polymorphic':'*'
    }

class ModelB(BaseModel):
    __tablename__ = 'modelB'
    __mapper_args__ = {'polymorphic_identity': 'modelB'}
    id = Column(Integer, ForeignKey('entities.id'), primary_key=True)
    name = Column(String, nullable=False)

class ModelC(BaseModel):
    __tablename__ = 'modelC'
    __mapper_args__ = {'polymorphic_identity': 'modelC'}
    id = Column(Integer, ForeignKey('entities.id'), primary_key=True)
    name = Column(String, nullable=False)

class ModelD(BaseModel):
    __tablename__ = 'modelD'
    __mapper_args__ = {'polymorphic_identity': 'modelD'}
    id = Column(Integer, ForeignKey('entities.id'), primary_key=True)
    name = Column(String, nullable=False)

class ModelE(ModelD):
    __tablename__ = 'modelE'
    __mapper_args__ = {'polymorphic_identity': 'modelE'}
    id = Column(Integer, ForeignKey('entities.id'), ForeignKey('modelD.id'), primary_key=True)
    name = Column(String, nullable=False)

class ModelF(ModelD):
    __tablename__ = 'modelF'
    __mapper_args__ = {'polymorphic_identity': 'modelF'}
    id = Column(Integer, ForeignKey('entities.id'), ForeignKey('modelD.id'), primary_key=True)
    name = Column(String, nullable=False)

answered Jun 4, 2013 at 7:55

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

amirpc Over a year ago

So this looks like it is piggy backing off of the discriminator column type_id on ModelA. In reality Models D E F are discriminated by another column - maybe call it type_inner_id. However ModelD must remain polymorphic in relation to ModelA on the type_id column.

van Over a year ago

I believe this is the way that makes sense, and definitely would not call it piggy backing. Great you found a solution which is acceptable for you though.

Ricky Levi Over a year ago

How can you now tie a ForeignKey from a class like "class Ricky(Base)" towards table "entities" in BaseModel(Base) ? it keeps give me: sqlalchemy.exc.NoReferencedTableError

van Over a year ago

What are the details of the error you get? And what is the model definition for it? As you can see from ModelE and ModelF above, you can define multiple foreign keys from the same column.

van Over a year ago

You do not always need to import the models in order to define relationships, because you can just specify class name or table name in the relationship definitions. Also take a look at this question/answer: stackoverflow.com/questions/7478403/….

|

Collectives™ on Stack Overflow

sqlalchemy nested inheritance / polymorphic relationships

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related