CODEWITHSUNDEEP
pythonsqlalchemyforeign-keysrelationship
domoarigato
domoarigato

Reputation: 2912

sqlalchemy relationship through an intermediate table

I have three related classes: Parent, Child, SubChild. The relationship is one-many in both cases. I've set it up so child.parent references correctly and of course so that sub_child.child.parent works as well.

The thing is that I never actually need to know sub_child.child, but I do need to know the sub_child's ultimate parent. I'd like to set up a relationship such that sub_child.parent would return a reference to the ultimate Parent object.

Is this possible, or is it just a bad idea? I've read the documentation, but don't see much that looks promising.

I'm using python2 and sqlalchemy orm on mysql as a backend.

Upvotes: 0

Views: 2456

Answers (1)

roman
roman

Reputation: 483

Have a look at http://docs.sqlalchemy.org/en/latest/orm/nonstandard_mappings.html

With this method you should be able to create a mapping over the three tables you mentioned and assign the columns of the participating tables as attributes to the mapping class.

metadata = MetaData()

parent = Table('parent', metadata,
        Column('id', Integer, primary_key=True),
        Column('child', Integer, ForeignKey('child.id')),
)
child = Table('child', metadata,
        Column('id', Integer, primary_key=True),
        Column('subchild', Integer, ForeignKey('subchild.id')),
)
subchild = Table('subchild', metadata,
        Column('id', Integer, primary_key=True),
        Column('some_column', String),
)
joined = join(parent, child, subchild)

Base = declarative_base()

class Parent(Base):
    __table__ = joined

    id = column_property(parent.c.id, child.c.id, subchild.c.id)
    subchild_attr = subchild.c.some_column

Upvotes: 1

Related Questions