Creating Self-referential Tables With Polymorphism In SQLALchemy

I'm trying to create a db structure in which I have many types of content entities, of which one, a Comment, can be attached to any other. Consider the following: from datetime imp

Solution 1:

Try to supplement your Comment.__mapper_args__ to:

__mapper_args__ = {
    'polymorphic_identity': 'comment',
    'inherit_condition': (id == Entity.id),
}

P.S. I haven't understand what you need _idref relationship for? If you want to use a comment somewhere where Entity is expected you could just pass the Comment instance as is.

Solution 2:

To supplement @nailxx's answer: Repeating all that boilerplate is tedious if you have many dependent tables. Answer : move it all to a metaclass.

class EntityMeta(type(Entity)):
    def __init__(cls, name, bases, dct):
        ident = dct.get('_identity',None)
        if '__abstract__' not in dct:
            xid = Column(None, ForeignKey(Entity.id), primary_key=True)
            setattr(cls,'id',xid)
            setattr(cls,'__mapper_args__', { 'polymorphic_identity': dct['_identity'], 'inherit_condition': (xid == Entity.id,), 'primary_key':(xid,) })
            setattr(cls,'__tablename__',name.lower())
        super(EntityMeta, cls).__init__(name, bases, dct)

class EntityRef(Entity):
    __abstract__ = True
    __metaclass__ = EntityMeta

class Comment(EntityRef):
    _identity = 'comment'
    [...]

Variations, as an exercise for the reader: You can omit the _identity=… statement and use the class name, as in the setattr(cls,'__tablename__',…) line. Or not overwrite an existing __tablename__ or __mapper_args__ attribute.

Be sure to test dct and not cls for existence: the latter will find attributes in the parent class, which you obviously do not want at this point.