This is, IMHO, one of the most complex parts of SQLAlchemy. In this public project, i have a handful of secondary/secondaryjoin examples that may help you
https://github.com/aptise/peter_sslers/blob/main/peter_sslers/model/objects.py#L3778-L4714 There is a section in the docs that should help a bit https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#composite-secondary-joins I think you want something like.... Person.photos = relationship( Photo, primaryjoin="""Person.id==PersonInstance.person_id""", secondary="""join(PersonInstance, PhotoInstance, PersonInstance.id==PhotoInstance.person_instance_id).join(Photo, PhotoInstance.photo_id == Photo.id)""", ) I don't think the secondaryjoin is needed in this case. I could be wrong. The way I like to structure these complex joins is something like this... A.Zs = relationship( Z, # the destination primaryjoin="""A.id == B.id""", # only the first association table secondary="""join(B.id == C.id).join(C.id == D.id)...(X.id==Y.id)""", # bring the rest of the tables in secondaryjoin=="""and_(Y.id==Z.id, Z.id.in(subselect))""" # custom filtering/join conditions ) Does that make sense? Mike has another way of explaining it in the docs, but this is how I best remember and implement it. On Sunday, February 7, 2021 at 3:25:35 PM UTC-5 daneb...@gmail.com wrote: > I am trying to create a relationship from one table to another, which > involves two intermediary tables. I *think* I need to use the secondaryjoin > + secondary arguments to relationship(). But after studying the > documentation for a long time, I can't get my head around how these > arguments are supposed to work. > > Here is my schema: > > class Person(Base): > __tablename__ = "person" > id = Column(Integer, primary_key=True) > > class PersonInstance(Base): > __tablename__ = "person_instance" > id = Column(Integer, primary_key=True) > person_id = Column(Integer, ForeignKey("person.id")) > > class Photo(Base): > __tablename__ = "photo" > id = Column(Integer, primary_key=True) > > class PhotoInstance(Base): > __tablename__ = "photo_instance" > id = Column(Integer, primary_key=True) > photo_id = Column(Integer, ForeignKey("photo.id")) > person_instance_id = Column(Integer, ForeignKey("person_instance.id")) > > I want to create a one-to-many relationship *Person.photos* which goes > from Person -> Photo. A Person is one-to-many with PersonInstance, and a > Photo is one-to-many with PhotoInstance objects. The connection from a > Person to a Photo exists between PersonInstance and PhotoInstance, via the > PhotoInstance.person_instance_id foreign key. > > First I tried using only primaryjoin: > > photos = relationship( > "Photo", > primaryjoin=( > "and_(Person.id==PersonInstance.person_id, " > "PersonInstance.id==PhotoInstance.person_instance_id, " > "PhotoInstance.photo_id==Photo.id)" > ) > ) > > I got an error saying it couldn't find the necessary foreign keys to > compute the join. > > So now I'm messing with secondary + secondaryjoin, but it's really trial & > error as I don't know how these arguments are supposed to work in my case. > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ff490d21-4e96-492a-a8ca-f953d1dd3e2fn%40googlegroups.com.
