"""Illustrate a "three way join" - where a primary table joins to a remotetable via an association table, but then the primary table also needsto refer to some columns in the remote table directly.E.g.:: first.first_id -> second.first_id second.other_id --> partitioned.other_id first.partition_key ---------------------> partitioned.partition_keyFor a relationship like this, "second" is a lot like a "secondary" table,but the mechanics aren't present within the "secondary" feature to allowfor the join directly between first and partitioned. Instead, wewill derive a selectable from partitioned and second combined together, thenlink first to that derived selectable.If we define the derived selectable as:: second JOIN partitioned ON second.other_id = partitioned.other_idA JOIN from first to this derived selectable is then:: first JOIN (second JOIN partitioned ON second.other_id = partitioned.other_id) ON first.first_id = second.first_id AND first.partition_key = partitioned.partition_keyWe will use the "non primary mapper" feature in order to produce this.A non primary mapper is essentially an "extra" :func:`.mapper` that we canuse to associate a particular class with some selectable that isnot its usual mapped table. It is used only when called upon withina Query (or a :func:`.relationship`)."""fromsqlalchemyimportand_fromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportIntegerfromsqlalchemyimportjoinfromsqlalchemyimportStringfromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportforeignfromsqlalchemy.ormimportmapperfromsqlalchemy.ormimportrelationshipfromsqlalchemy.ormimportSessionBase=declarative_base()classFirst(Base):__tablename__="first"first_id=Column(Integer,primary_key=True)partition_key=Column(String)def__repr__(self):return"First(%s, %s)"%(self.first_id,self.partition_key)classSecond(Base):__tablename__="second"first_id=Column(Integer,primary_key=True)other_id=Column(Integer,primary_key=True)classPartitioned(Base):__tablename__="partitioned"other_id=Column(Integer,primary_key=True)partition_key=Column(String,primary_key=True)def__repr__(self):return"Partitioned(%s, %s)"%(self.other_id,self.partition_key)j=join(Partitioned,Second,Partitioned.other_id==Second.other_id)partitioned_second=mapper(Partitioned,j,non_primary=True,properties={# note we need to disambiguate columns here - the join()# will provide them as j.c.<tablename>_<colname> for access,# but they retain their real names in the mapping"other_id":[j.c.partitioned_other_id,j.c.second_other_id]},)First.partitioned=relationship(partitioned_second,primaryjoin=and_(First.partition_key==partitioned_second.c.partition_key,First.first_id==foreign(partitioned_second.c.first_id),),innerjoin=True,)# when using any database other than SQLite, we will get a nested# join, e.g. "first JOIN (partitioned JOIN second ON ..) ON ..".# On SQLite, SQLAlchemy needs to render a full subquery.e=create_engine("sqlite://",echo=True)Base.metadata.create_all(e)s=Session(e)s.add_all([First(first_id=1,partition_key="p1"),First(first_id=2,partition_key="p1"),First(first_id=3,partition_key="p2"),Second(first_id=1,other_id=1),Second(first_id=2,other_id=1),Second(first_id=3,other_id=2),Partitioned(partition_key="p1",other_id=1),Partitioned(partition_key="p1",other_id=2),Partitioned(partition_key="p2",other_id=2),])s.commit()forrowins.query(First,Partitioned).join(First.partitioned):print(row)forfins.query(First):forpinf.partitioned:print(f.partition_key,p.partition_key)