[sqlalchemy] Self-referential relationship with multiple intermediary tables

[Dane K Barney]

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.