Relationship with 2 intermediary tables

[Dane K Barney]

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.

[Jonathan Vanasco]

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.