I'm not able to reproduce with SQLAlhcemy 1.4. The first join condition given works and if I print a query as follows:
s = Session() acc_alias = aliased(Account) print( s.query(Account).join(Account.downstream_accounts.of_type(acc_alias)) ) SQL output is: SELECT account.id AS account_id FROM account JOIN (content AS content_1 JOIN content_connection AS content_connection_1 ON content_1.id = content_connection_1.source_id JOIN content AS content_2 ON content_connection_1.dest_id = content_1.id) ON account.id = content_1.account_id JOIN account AS account_1 ON content_1.account_id = account_1.id see runnable POC below from sqlalchemy import Column from sqlalchemy import ForeignKey from sqlalchemy import Integer from sqlalchemy.orm import aliased from sqlalchemy.orm import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.orm import Session Base = declarative_base() class Account(Base): __tablename__ = 'account' id = Column(Integer, primary_key=True) class Content(Base): __tablename__ = 'content' id = Column(Integer, primary_key=True) account_id = Column(Integer, ForeignKey("account.id")) class ContentConnection(Base): __tablename__ = 'content_connection' id = Column(Integer, primary_key=True) source_id = Column(Integer, ForeignKey("content.id")) dest_id = Column(Integer, ForeignKey("content.id")) 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, ) s = Session() acc_alias = aliased(Account) print( s.query(Account).join(Account.downstream_accounts.of_type(acc_alias)) ) On Sat, Feb 12, 2022, at 3:46 PM, Dane K Barney wrote: > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/2803fcaf-d7fc-48e5-a07a-e6bad84fe879n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/34b45379-d330-4be2-92d2-6b72984e9960%40www.fastmail.com.