SqlAlchemy Relationship from one "join" table to another "join" table

Question

I am testing out setting up SQLAlchemy to map an existing database. This database was set up a long time ago, automatically, by a previous 3rd party application which we are no longer using, so some expected things like foreign key constraints are not defined. The software would manage all of those relationships.

It is a node-ish type production tracking database structure, with parent-child relationships. The nodetype-specific data lives in their own tables, and there is a main relationship table for the common columns like type, uid, parentUid, ...

The structure is like this...

1. A hierarchy table that contains basically every node entry, with a primary key "uid", a "type" enum, and a "parentUid" to reference a parent node.
2. NodeA / NodeB / ... table has a "uid" that one-to-one matches the hierarchy table

What I have gathered from the SQLAlchemy docs is that I should try to do "join" tables. Here is what I have so far:

# has a column called 'parentUid'
hierarchy = Table('hierarchy', METADATA, autoload=True)

hier_fk = lambda: Column('uid', 
                          Integer, 
                          ForeignKey('hierarchy.uid'), 
                          primary_key=True)

nodeTypeA = Table('nodetype_a', METADATA, nodehier_fk(), autoload=True)
nodeTypeB = Table('nodetype_b', METADATA, nodehier_fk(), autoload=True)

Base = declarative_base()

class NodeA(Base):
    __table__ = join(hierarchy, nodeTypeA)

    id = column_property(hierarchy.c.uid, nodeTypeA.c.uid)
    uid = nodeTypeA.c.uid


class NodeB(Base):
    __table__ = join(hierarchy, nodeTypeB)

    id = column_property(hierarchy.c.uid, nodeTypeB.c.uid)
    uid = nodeTypeB.c.uid

    # cannot figure this one out
    parent = relationship("NodeA", primaryjoin="NodeB.parentUid==NodeA.id")

The relationship is clearly wrong and crashes. I have tried a bunch of combinations of defining the foreign_keys attributes, and using a mixture of the hierarchy.c.uid style approaches. But I just can't grasp how to make the relationship to this other table.

Without the relationship line, the queries work great. I get the full representation of each Node joined on the hierarchy table. I can even manually get the NodeA parent of NodeB by doing:

node_a = session.query(NodeA).filter_by(uid=node_b.parentUid).first()

Is the "join" approach appropriate for my goal? How can I get this relationship working?

Update

I have managed to get a one-way relationship working so far with the following:

children = relationship("NodeB", 
                        primaryjoin="NodeB.parentUid==NodeA.id", 
                        foreign_keys=[hierarchy.c.parentUid],
                        # backref="parent"
            )

But if I uncomment the backref to have it put the reverse on NodeB, I get this:

ArgumentError: NodeA.children and back-reference NodeB.parent are both of the same direction . Did you mean to set remote_side on the many-to-one side ?

Answer 1

remote_side is used in self-referential relationships to distinguish which side is "remote". The flag is described in http://docs.sqlalchemy.org/en/latest/orm/relationships.html#adjacency-list-relationships. Because you're mapping classes directly to a join(), SQLAlchemy considers each join() to be the mapped table, and the "is self referential" condition is detected because both joins depend on the same base table. If you were to build this mapping using the usual pattern of joined table inheritance (see http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance), the relationship() would have a little more context with which to figure out how to join without the explicit remote_side argument.

Full example using the given approach:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

e = create_engine("sqlite://", echo=True)

e.execute("""
create table hierarchy (uid int primary key, parentUid int)
""")

e.execute("""
create table nodetype_a (uid int primary key)
""")

e.execute("""
create table nodetype_b (uid int primary key)
""")

Base = declarative_base()


# has a column called 'parentUid'
hierarchy = Table('hierarchy', Base.metadata, autoload=True, autoload_with=e)

nodehier_fk = lambda: Column('uid',
                          Integer,
                          ForeignKey('hierarchy.uid'),
                          primary_key=True)

nodeTypeA = Table('nodetype_a', Base.metadata, nodehier_fk(), autoload=True, autoload_with=e)
nodeTypeB = Table('nodetype_b', Base.metadata, nodehier_fk(), autoload=True, autoload_with=e)

Base = declarative_base()

class NodeA(Base):
    __table__ = join(hierarchy, nodeTypeA)

    id = column_property(hierarchy.c.uid, nodeTypeA.c.uid)
    uid = nodeTypeA.c.uid


class NodeB(Base):
    __table__ = join(hierarchy, nodeTypeB)

    id = column_property(hierarchy.c.uid, nodeTypeB.c.uid)
    uid = nodeTypeB.c.uid

    # cannot figure this one out
    parent = relationship("NodeA",
                    primaryjoin="NodeB.parentUid==NodeA.id",
                    foreign_keys=hierarchy.c.parentUid,
                    remote_side=hierarchy.c.uid,
                    backref="children")

s = Session(execute)
s.add_all([
        NodeA(children=[NodeB(), NodeB()])
])
s.commit()