Self referential relationship including a relationship attribute

Question

Situation

I have the Self-Referential Many-to-Many Relationship (almost identical to the sqlalchemy manual entry of the same heading). This relationship is governed by the table entity_weights. This code works!

Question

How do I include an attribute in class Entity representing the table column entity_weights.weight. Lets say the attribute would be called Entity.child_weights. It is important that the rank of Entity.child_entities and Entity.child_weights are identical.

entity_weights = Table('entity_weights', Base.metadata,
    Column('id',Integer, primary_key=True),
    Column('parent_entity_id',Integer, ForeignKey('entity.id')),
    Column('entity_id',Integer, ForeignKey('entity.id')),
    Column('weight',Float))


class Entity(Base):
    __tablename__ = 'entity'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    domicile_id = Column(Integer, ForeignKey('domicile.id'))
    entity_type = Column('type',Enum('asset','institution','model'))
    source_table_id = Column(Integer)
    child_entities = relationship('Entity',
                        secondary=entity_weights,
                        primaryjoin=id==entity_weights.c.parent_entity_id,
                        secondaryjoin=id==entity_weights.c.entity_id,
                        backref='parent_entity_id'
                        )

Answer 1

The cleanest solution I've found for this scenario is to break up the child_entities relationship by adding entity_weights as a one-to-many relationship on Entity and use an association proxy to proxy the weight value as well as the remote side of the many-to-many relationship:

class EntityWeight(Base):
    __tablename__ = "entity_weights"
    id = Column(Integer, primary_key=True)
    parent_entity_id = Column(Integer, ForeignKey('entity.id'))
    entity_id = Column(Integer, ForeignKey('entity.id'))
    weight = Column(Float)
    entity = relationship("Entity", primaryjoin=lambda: EntityWeight.entity_id == Entity.id)

class Entity(Base):
    ...
    _child_weights = relationship(EntityWeight, primaryjoin=id == EntityWeight.parent_entity_id)
    child_weights = association_proxy("_child_weights", "weight")
    child_entities = association_proxy("_child_weights", "entity")