Many-to-many relationship on same table with association object
- by Nicholas Knight
Related (for the no-association-object use case): http://stackoverflow.com/questions/1889251/sqlalchemy-many-to-many-relationship-on-a-single-table
Building a many-to-many relationship is easy. Building a many-to-many relationship on the same table is almost as easy, as documented in the above question.
Building a many-to-many relationship with an association object is also easy.
What I can't seem to find is the right way to combine association objects and many-to-many relationships with the left and right sides being the same table.
So, starting from the simple, naïve, and clearly wrong version that I've spent forever trying to massage into the right version:
t_groups = Table('groups', metadata,
Column('id', Integer, primary_key=True),
)
t_group_groups = Table('group_groups', metadata,
Column('parent_group_id', Integer, ForeignKey('groups.id'), primary_key=True, nullable=False),
Column('child_group_id', Integer, ForeignKey('groups.id'), primary_key=True, nullable=False),
Column('expires', DateTime),
)
mapper(Group_To_Group, t_group_groups, properties={
'parent_group':relationship(Group),
'child_group':relationship(Group),
})
What's the right way to map this relationship?
