[sqlalchemy] 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.

-- 
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/cba91131-2d33-45ac-bd96-c64910aeb9f1n%40googlegroups.com.