I have a somewhat advanced relationship that I'm trying to define, from a table to itself, but which involves passing through multiple tables to get there.
These are my tables: class Account(Base): id = Column(Integer, primary_key=True) class Content(Base): id = Column(Integer, primary_key=True) account_id = Column(Integer, ForeignKey("account.id")) class ContentConnection(Base): id = Column(Integer, primary_key=True) source_id = Column(Integer, ForeignKey("content.id")) dest_id = Column(Integer, ForeignKey("content.id")) The relationship I want to define is Account.downstream_accounts which is effectively: Account -> Content -> ContentConnection -> Content -> Account using these join conditions: Account.id==Content.account_id, Content.id==ContentConnection.source_id, ContentConnection.dest_id==Content.id, Content.account_id==Account.id The problem here is obviously the ambiguity of both the Account and Content tables in these joins. I'm struggling to come up with the right combination of "primaryjoin", "secondary", and "secondaryjoin" (and possibly "foreign_keys" / "remote_side") arguments to get this to work. Attempt #1: Account.downstream_accounts = relationship( "Account", primaryjoin="Account.id==Content.account_id", secondary=( "join(Content, ContentConnection, Content.id==ContentConnection.source_id)" ".join(Content, ContentConnection.dest_id==Content.id)" ), secondaryjoin="Content.account_id==Account.id", viewonly=True, ) This complains that the Content table is not unique in the "secondary" argument. So I tried to move the complexity out of the "secondary" argument and into the "primaryjoin"/"secondaryjoin" arguments. Attempt #2: Account.downstream_accounts = relationship( "Account", primaryjoin="and_(Account.id==Content.account_id, Content.id==ContentConnection.source_id)", secondary="content_connection", secondaryjoin= "and_(Account.id==Content.account_id, Content.id==ContentConnection.dest_id)" , viewonly=True, ) This complains about ambiguous local/remote column pairs and suggests that I use the remote() annotation. But I'm not sure I'm on the right track here and I have no idea where I would put any remote() annotation since I don't really understand its purpose. Any help would be greatly appreciated! Thanks -- 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/2803fcaf-d7fc-48e5-a07a-e6bad84fe879n%40googlegroups.com.