ORM AmbiguousForeignKeysErro

[Richard Damon]

Following code and error I am getting:


class Base:
    """Base Class for SQLAlchemy ORM Classes"""
    @declared_attr
    def __tablename__(cls):
        """Default the Table Name to the Class Name"""
        return cls.__name__


Base = declarative_base(cls=Base)


class Node(Base):
    """Class represents the base of the User Data types."""

    node_id = Column(Integer, primary_key=True)
    type_name = Column(String(255), nullable=False)  # todo should come
from type_id

    __mapper_args__ = {
        'polymorphic_identity': 'Node',
        'polymorphic_on': type_name,
    }


class Property(Node):
    """Class represents a Node providing information about another Node"""
    node_id = Column(Integer, ForeignKey('Node.node_id'), primary_key=True)
    ref_id = Column(Integer, ForeignKey('Node.node_id'))

    __mapper_args__ = {
        'polymorphic_identity': 'Property',
    }

Error:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'Node' and 'Property'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.


Other classes driving from node don't have this issue, but then they
don't have that second foreign key back to node. Can't figure out how to
sepecify the onclause, since I don't explicitly give the join., and my
searching-foo isn't finding anything on this. I suspect somewhere,
likely in the __mapper_args__ I need to specify the field to join on,
but can't find it.


On a somewhat related note, for purposes of DRY, since all of the
subclasses have the same node_id declaration, and basically the same
__mapper__ is there a way I can add this to Node to push this into the
subclasses? Would I use a @declared_attr, which would need to check if
the type was Node since it is different?

(Background, long time programmer, but somewhat new to python, looking
to learn how with reasons, not just rote recipes to follow)

--
Richard Damon

[Mike Bayer]

the argument you're looking for is inherit_condition:

https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition

class Foo(...):

   __mapper_args__ = {

      "inherit_condition": node_id == Node.node_id

-- 

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/bd6dcf30-f6ee-308d-284f-14aeef4505ae%40Damon-Family.org.

[Richard Damon]

Thank you, so that go into each subclass that would have the problem.

 8/28/20 2:37 PM, Mike Bayer wrote:
> the argument you're looking for is inherit_condition:
>
> https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
>
>
> class Foo(...):
>    __mapper_args__ = {
>       "inherit_condition": node_id == Node.node_id
>    }
>

--
Richard Damon

[maqui...@gmail.com]

I'm having the same problem,

I have a base class called TrackedEntity that has child classes like Request and others that inherit from it

on Request I wanted to put a reference to the id of the TrackedEntity that created the Request

class Request(TrackedEntity, TrackedEntityContainer, VisibleIdMixin):
     parent_tracked_entity_id = Column(UUID, ForeignKey("tracked_entity.id"))

and I get the same error as above.  Adding that inherit condition makes the runtime error stop, but it doesn't make sense to me. Why can't I just have a foreign key to that table? It's a simple many to one

@Richard: you can use @declared_attr.cascading to cascade the mapper_args to your child classes.

[Richard Damon]

The key point is that the derived class needs a pointer to its base
class for the inheritance, and if it has another one to represent object
linkage, then the ORM module doesn't know which one is which, in my case
even though they were all called node_id, the fact one of the classes
had another reference it didn't know which to use, thus you need to put
a __mapper_args__ with an entry for "inherit_condition" to indicate
which on to use for inheritance. Once you resolve the inheritance
problem, the rest can be used for object relationships.

--
Richard Damon

[Mark Aquino]

I’m not using that FK for inheritance though. I’m just relating one type of tracked entity to another (it’s parent, basically). After I did this it actually broke my code so it didn’t really work (it just temporarily got rid of one error and caused a more complicated one)

Mark Aquino

---

From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Richard Damon <Ric...@Damon-Family.org>
Sent: Tuesday, September 8, 2020 5:10:52 PM
To: sqlal...@googlegroups.com <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] ORM AmbiguousForeignKeysErro

 

--
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/8f002a3f-fb68-5f8b-c921-2dddaceb1119%40Damon-Family.org.

[Mike Bayer]

the error is raised because there is more than one column on your subclass table that is a foreign key to the superclass table.   SQLAlchemy refuses to guess which of these columns it should use to create the join condition between superclass and subclass table.

--

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/a515b477-5308-4609-af86-c0fb671151cen%40googlegroups.com.

[Mark Aquino]

So if I’m understanding correctly then the inherit_condition should be the column mapping the subclass to the superclass? In my case TrackedEntity.id == Request.id?

Mark Aquino

---

From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Mike Bayer <mik...@zzzcomputing.com>
Sent: Tuesday, September 8, 2020 9:01:53 PM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>

Subject: Re: [sqlalchemy] ORM AmbiguousForeignKeysErro

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b21d61c4-8221-4274-851f-363b4d098ae6%40www.fastmail.com.

[Richard Damon]

On 9/8/20 8:02 PM, Mark Aquino wrote:
> I’m not using that FK for inheritance though. I’m just relating one
> type of tracked entity to another (it’s parent, basically). After I
> did this it actually broke my code so it didn’t really work (it just
> temporarily got rid of one error and caused a more complicated one)
>

I think you need to post the basics of the code. See the link below
about making it a MCVE

There is obviously something you aren't describing, or we need to see to
point to you what you are missing.

--
Richard Damon

[Mark Aquino]

It's okay, I misunderstood what to point to in the inherit_condition argument.  I pointed it to the right column and it's working now.

--
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/414307fa-8779-5be1-244c-57c1cf9d7a02%40Damon-Family.org.