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.
