@declared_attr.cascading doesn't work when inheritance of multiple levels

[niuji...@gmail.com]

I'm using the "joined table inheritance" model. I have three levels of inheritance.

class has_polymorphic_id(object):

    @declared_attr.cascading

    def record_id(cls):

        if has_inherited_table(cls):

            return Column(ForeignKey('employee.record_id'),

                          primary_key=True)

        else:

            return Column(Integer, primary_key=True)

class Employee(has_polymorphic_id, Base):

    __tablename__ = 'employee'

    name = Column(String(50))

    type = Column(String(50))

    __mapper_args__ = {

        'polymorphic_identity':'employee',

        'polymorphic_on':type

    }

class Engineer(Employee):

    __tablename__ = 'engineer'

    ....

class Programmer(Engineer):

    __tablename__ = 'programmer'

    ....

This only works for the second level, namely `Enginner` can inherits the foreignkey/primarykey from `Employee`'s mixin, but the next level, the `Programmer`, python gives me an error:

`sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'engineer' and 'programmer'.`

Is this designed this way? And if I manually set the foreignkey, should the third level reference to the base level or to its immediate parent level's primarykey?

[Mike Bayer]

The "cascading" attribute seems to be working correctly.  The error here is because you aren't providing any column that will allow for a JOIN between the "programmer" and "engineer" table.

you would want Programmer.record_id to be a foreign key to Engineer.record_id, not Employee.record_id.    When you load Programmer rows, the join would be "FROM employee JOIN engineer ON <onclause> JOIN programmer ON <onclause>".

Is this designed this way? And if I manually set the foreignkey, should the third level reference to the base level or to its immediate parent level's primarykey?

it has to be to the immediate parent.  that's what the error message here is talking about.

--

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/02ce8134-2946-4d1b-b6d3-e47ad7944e0en%40googlegroups.com.

[niuji...@gmail.com]

I've just manually put this line to the `Programmer` class definition, but it still gives me the same error, strangely:

class Programmer(Engineer):

    __tablename__ = 'programmer'

    record_id = Column(ForeignKey('engineer.record_id'),

                       primary_key=True)

    ....

[Mike Bayer]

this is addressed in the docs which discuss "cascading" here:

https://docs.sqlalchemy.org/en/14/orm/declarative_mixins.html#mixing-in-columns-in-inheritance-scenarios

"The declared_attr.cascading feature currently does not allow for a subclass to override the attribute with a different function or value. This is a current limitation in the mechanics of how @declared_attr is resolved, and a warning is emitted if this condition is detected. This limitation does not exist for the special attribute names such as __tablename__, which resolve in a different way internally than that of declared_attr.cascading."

you will see a warning in your program's output:

SAWarning: Attribute 'record_id' on class <class '__main__.Programmer'> cannot be processed due to @declared_attr.cascading; skipping

as a workaround, you can look for the attribute in __dict__ and return it, though you still get the warning:

    class has_polymorphic_id(object):

        @declared_attr.cascading

        def record_id(cls):

            if "record_id" in cls.__dict__:

                return cls.__dict__['record_id']

            elif has_inherited_table(cls):

                return Column(ForeignKey("employee.record_id"), primary_key=True)

            else:

                return Column(Integer, primary_key=True)

otherwise you'd want to look at "cls" with inspect(cls).local_table etc. and figure out the correct column to include in the FK if you are doing things this way.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b08ba9e6-e1e3-451e-b39b-bc5ee4b63847n%40googlegroups.com.

[niuji...@gmail.com]

Thanks for pointing this out. It did address this problem already.

I just solved this by manually adding all the primary/foreign keys to each classes. Not a big deal, actually it helps clear code!

[Mike Bayer]

yeah I am not a huge fan of declared_attr.cascading except for maybe a table name convention

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dfeef924-d1c9-483d-b293-5431cd0940ben%40googlegroups.com.