declarative Association table w/ foreign_keys in relationship() still raises "multiple foreign key paths linking the tables"

[b...@sfi.ca]

Hi,

Running SQLAlchemy==1.1.4

I'm getting this

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Artifact.slaves - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

and I'm wondering if I'll have to use primaryjoin= statement instead of relying on foreign_keys= to work around this.

The code is shown below. I've included Artifact_Reference for completeness but it's probably not needed.

Is the problem on the Artifact_Relation model, or on the Artifact model? I'm thinking the later, but I don't see any logical way to specify foreign_keys on Artifact as it's on the one side of many to one. 

I'm following this link for Artifact_Relation (i.e. Association table) http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object  where Artifact serves as both the Parent and Child

I'm following this link for multiple-path resolution http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-foreign-keys

Even though Artifact is on both sides of the association, I can't see needing to use backref()  w/ remote_id to resolve this, ala http://docs.sqlalchemy.org/en/latest/orm/self_referential.html

Thanks for any suggestions..

Base = declarative_base()

class Artifact_Relation(Base):

    __tablename__ = 'artifact_relation'

    master_artifact_id = Column(

        Integer,

        ForeignKey('artifact.id', name='artifact_relation_master_id_fk', ondelete="cascade", onupdate="cascade"),

        primary_key=True,

        nullable=False

    )

    slave_artifact_id = Column(

        Integer,

        ForeignKey('artifact.id', name='artifact_relation_slave_id_fk', ondelete="cascade", onupdate="cascade"),

        primary_key=True,

        nullable=False

    )

    relationship_type = Column(String(24), nullable=False)

    slave = relationship("Artifact", back_populates="masters", foreign_keys=[slave_artifact_id])

    master = relationship("Artifact", back_populates="slaves", foreign_keys=[master_artifact_id])

class Artifact(Base):

    "Artifact"

    __tablename__ = 'artifact'

    id = Column('id', Integer, primary_key=True)

    artifact_type = Column('artifact_type', String(16), nullable=False)

    __mapper_args__ = {

        'polymorphic_on': artifact_type

    }

    artifact_references = relationship(

        'Artifact_Reference',

        back_populates='artifact',

        cascade="all, delete-orphan",

        passive_deletes=True

    )

    slaves = relationship("Artifact_Relation", back_populates="master")

    masters = relationship("Artifact_Relation", back_populates="slave")

class Artifact_Reference(Base):

    """Artifact_Reference"""

    __tablename__ = 'artifact_reference'

    id = Column('id', Integer, primary_key=True)

    type = Column('type', String(24), nullable=False)

    artifact_id = Column('artifact_id', Integer, ForeignKey(Artifact.id, name="artifact_reference_artifact_id_fk", ondelete="cascade", onupdate="cascade"), nullable=False)

    reference = Column('reference', String(64), nullable=False)

    artifact = relationship(

        Artifact,

        back_populates='artifact_references'

    )

    Index('reference_idx', reference, unique=False)

 

[mike bayer]

On 12/08/2016 08:44 PM, b...@sfi.ca wrote:
> Hi,
>
> Running SQLAlchemy==1.1.4
>
> I'm getting this
>
> AmbiguousForeignKeysError: Could not determine join condition
> between parent/child tables on relationship Artifact.slaves - there
> are multiple foreign key paths linking the tables. Specify the
> 'foreign_keys' argument, providing a list of those columns which
> should be counted as containing a foreign key reference to the
> parent table.
>
>
> and I'm wondering if I'll have to use primaryjoin= statement instead of
> relying on foreign_keys= to work around this.
>
> The code is shown below. I've included Artifact_Reference for
> completeness but it's probably not needed.

when you build two relationship()s that are linked using back_populates,
the configuration for each relationship() has to be set up completely as
though that relationship were on its own. The one-to-many and
many-to-one relationships from a "foreign key" perspective are
essentially identical, they refer to the same structure in the database.
So in this specific example you need "foreign_keys" on both sides:

slaves = relationship("Artifact_Relation", back_populates="master",
foreign_keys="Artifact_Relation.slave_artifact_id")
masters = relationship("Artifact_Relation", back_populates="slave",
foreign_keys="Artifact_Relation.master_artifact_id")


if you were to instead build relationship on just one side, and the
other one were generated by "backref", then you wouldn't need this
double-configuration.

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[b...@sfi.ca]

Thanks Mike for the quick reply.

I have changed the code so it uses backref on one side. The models now get created w/o error. I haven't actually tested using the relationship but I'm sure it's fine now.

For anyone else with this problem, here's the 'fixed' code. I didn't change the Artifact_Reference table, it's the same as posted above. I'm not keen on the naming master/slave.. maybe primary/secondary would be better.. It's not exactly Parent/Child.. heh.

class Artifact_Relation(Base):

    __tablename__ = 'artifact_relation'

    master_artifact_id = Column(

        Integer,

        ForeignKey('artifact.id', name='artifact_relation_master_id_fk', ondelete="cascade", onupdate="cascade"),

        primary_key=True,

        nullable=False

    )

    slave_artifact_id = Column(

        Integer,

        ForeignKey('artifact.id', name='artifact_relation_slave_id_fk', ondelete="cascade", onupdate="cascade"),

        primary_key=True,

        nullable=False

    )

    relationship_type = Column(String(24), nullable=False)

    slave = relationship("Artifact", backref=backref("masters"), foreign_keys=[slave_artifact_id])

    master = relationship("Artifact", backref=backref("slaves"), foreign_keys=[master_artifact_id])

[mike bayer]

leader/follower is often used in this case.

[b...@sfi.ca]

That's a good suggestion, thanks.