CODEWITHSUNDEEP
pythonsqlalchemy
amirpc
amirpc

Reputation: 1668

sqlalchemy nested inheritance / polymorphic relationships

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:

              A
          /   |   \
          B   C   D
                /   \
                E    F

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.

Upvotes: 4

Views: 2885

Answers (1)

van
van

Reputation: 76952

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)

Upvotes: 5

Related Questions