In sqlalchemy, how can I use polymorphic joined table inheritance when the child table has multiple foreign keys to the parent table?

Question

I have a parent table called pbx_point that has a point_type column. I also have a child table called pbx_route, with a column called point_id pointing back to pbx_point.

I'd like to use sqlalchemy's joined table inheritance to relate these two tables via declarative base, and use polymorphic inheritance

This works fine - or rather, it would, if not for the following additional constraint: pbx_point also has a foreign key called initial_route_id pointing to pbx_route.

I'm also using reflection below, but the db is as I described above. The error I get is sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'pbx_point' and 'pbx_route'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly..

This makes sense, as "behind the scenes" delcarative base is creating a relationship() attribute on both mapped classes. I'd like it to choose pbx_route.point_id as the parent_id link, but it also sees the pbx_point.initial_route_id column. This would be simple to fix, if I was creating this relationship(), but I am not - the declarative inheritance system is.

Is there an additional argument I can pass to __mapper_args__, such as polymorphic_parent_col which would let me specify the foreign key I desire? If not, how can I work around this issue?

Thanks.

class MyBase(DeferredReflection):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

Base = declarative_base(cls=MyBase)

class pbx_point(Base):
    __mapper_args__ = dict(
        polymorphic_on='point_type',
        with_polymorphic='*',
    )

class pbx_route(pbx_point):
    __mapper_args__ = dict(polymorphic_identity='pbx.route')

This is the stack trace I am getting:

Traceback (most recent call last):
  File "db.py", line 50, in <module>
    Base.prepare(engine)
  File "env/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py", line 431, in prepare
    thingy.map()
  File "env/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 379, in map
    **mapper_args
  File "env/local/lib/python2.7/site-packages/sqlalchemy/orm/__init__.py", line 1147, in mapper
    return Mapper(class_, local_table, *args, **params)
  File "env/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 213, in __init__
    self._configure_inheritance()
  File "env/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 517, in _configure_inheritance
    self.local_table)
  File "env/local/lib/python2.7/site-packages/sqlalchemy/sql/util.py", line 397, in join_condition
    "join explicitly." % (a.description, b.description))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'pbx_point' and 'pbx_route'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Which indicates it is dying at line 1032 in mapper.py*. A few lines above that makes reference to the mapper kwarg inherit_condition , which appears to be what I need.

[*]: Source link adjusted to version 1.3.11 (previous URL now 404s)

Answer 1

The key is the inherit_condition argument to the mapper. The polymorphic information doesn't actually have anything to do with this step.

Corrected models:

class MyBase(DeferredReflection):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

Base = declarative_base(cls=MyBase)

class pbx_point(Base):
    __mapper_args__ = dict(
        polymorphic_on='point_type',
        with_polymorphic='*',
    )
    id = Column(Integer, primary_key=True)

class pbx_route(pbx_point):
    point_id = Column(Integer, ForeignKey(pbx_point.id))
    __mapper_args__ = dict(
        polymorphic_identity='pbx.route',
        inherit_condition=(point_id == pbx_point.id)
    )

I needed to add the id and point_id columns to use them in the inherit_condition argument. There is likely to be a way to do this only using reflection, but this is not a terrible hindrance.