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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/2803fcaf-d7fc-48e5-a07a-e6bad84fe879n%40googlegroups.com.
