I am trying to build a relationship to another many-to-many relationship, the code looks like this:

from sqlalchemy import Column, Integer, ForeignKey, Table, ForeignKeyConstraint, create_engine
from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()supervision_association_table = Table('supervision', Base.metadata,Column('supervisor_id', Integer, ForeignKey('supervisor.id'), primary_key=True),Column('client_id', Integer, ForeignKey('client.id'), primary_key=True)
)class User(Base):__tablename__ = 'user'id = Column(Integer, primary_key=True)class Supervisor(User):__tablename__ = 'supervisor'__mapper_args__ = {'polymorphic_identity': 'supervisor'}id = Column(Integer, ForeignKey('user.id'), primary_key = True)schedules = relationship("Schedule", backref='supervisor')class Client(User):__tablename__ = 'client'__mapper_args__ = {'polymorphic_identity': 'client'}id = Column(Integer, ForeignKey('user.id'), primary_key = True)supervisor = relationship("Supervisor", secondary=supervision_association_table,backref='clients')schedules = relationship("Schedule", backref="client")class Schedule(Base):__tablename__ = 'schedule'__table_args__ = (ForeignKeyConstraint(['client_id', 'supervisor_id'], ['supervision.client_id', 'supervision.supervisor_id']),)id = Column(Integer, primary_key=True)client_id = Column(Integer, nullable=False)supervisor_id = Column(Integer, nullable=False)engine = create_engine('sqlite:///temp.db')
db_session = scoped_session(sessionmaker(bind=engine))
Base.metadata.create_all(bind=engine)

What I want to do is to relate a schedule to a specific Client-Supervisor-relationship, though I have not found out how to do it. Going through the SQLAlchemy documentation I found a few hints, resulting in the ForeignKeyConstraint on the Schedule-Table.

How can I specify the relationship to have this association work?

You need to map supervision_association_table so that you can create relationships to/from it.

I may be glossing over something here, but it seems like since you have many-to-many here you really can't have Client.schedules - if I say Client.schedules.append(some_schedule), which row in "supervision" is it pointing to?

The example below provides a read-only "rollup" accessor for those which joins the Schedule collections of each SupervisorAssociation. The association_proxy extension is used to conceal, when convenient, the details of the SupervisionAssociation object.

from sqlalchemy import Column, Integer, ForeignKey, Table, ForeignKeyConstraint, create_engine
from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from itertools import chainBase = declarative_base()class SupervisionAssociation(Base):__tablename__ = 'supervision'supervisor_id = Column(Integer, ForeignKey('supervisor.id'), primary_key=True)client_id = Column(Integer, ForeignKey('client.id'), primary_key=True)supervisor = relationship("Supervisor", backref="client_associations")client = relationship("Client", backref="supervisor_associations")schedules = relationship("Schedule")class User(Base):__tablename__ = 'user'id = Column(Integer, primary_key=True)class Supervisor(User):__tablename__ = 'supervisor'__mapper_args__ = {'polymorphic_identity': 'supervisor'}id = Column(Integer, ForeignKey('user.id'), primary_key = True)clients = association_proxy("client_associations", "client", creator=lambda c: SupervisionAssociation(client=c))@propertydef schedules(self):return list(chain(*[c.schedules for c in self.client_associations]))class Client(User):__tablename__ = 'client'__mapper_args__ = {'polymorphic_identity': 'client'}id = Column(Integer, ForeignKey('user.id'), primary_key = True)supervisors = association_proxy("supervisor_associations", "supervisor", creator=lambda s: SupervisionAssociation(supervisor=s))@propertydef schedules(self):return list(chain(*[s.schedules for s in self.supervisor_associations]))class Schedule(Base):__tablename__ = 'schedule'__table_args__ = (ForeignKeyConstraint(['client_id', 'supervisor_id'], ['supervision.client_id', 'supervision.supervisor_id']),)id = Column(Integer, primary_key=True)client_id = Column(Integer, nullable=False)supervisor_id = Column(Integer, nullable=False)client = association_proxy("supervisor_association", "client")engine = create_engine('sqlite:///temp.db', echo=True)
db_session = scoped_session(sessionmaker(bind=engine))
Base.metadata.create_all(bind=engine)c1, c2 = Client(), Client()
sp1, sp2 = Supervisor(), Supervisor()
sch1, sch2, sch3 = Schedule(), Schedule(), Schedule()sp1.clients = [c1]
c2.supervisors = [sp2]
c2.supervisor_associations[0].schedules = [sch1, sch2]
c1.supervisor_associations[0].schedules = [sch3]db_session.add_all([c1, c2, sp1, sp2, ])
db_session.commit()print c1.schedules
print sp2.schedules