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

[Mike Bayer]

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))

)

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2803fcaf-d7fc-48e5-a07a-e6bad84fe879n%40googlegroups.com.