Problem with prescribed polymorphism solution

21 views
Skip to first unread message

Isaac Martin

unread,
Mar 3, 2020, 3:14:29 PM3/3/20
to sqlalchemy
SQLalchemy prescribes a pattern for dealing with polymorphic relationships which are one to many. You can see this pattern here: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.AbstractConcreteBase

After implementing this pattern I was pleased to find that it correctly created my tables for the child classes, did not create a table for the parent class, and successfully loads all children into a single collection on the one side of the one to many. Basically everything I wanted.

Unfortunately, I quickly came to discover that all child classes had all the fields of all the other child classes.  This doesn't happen on the tables, it is only present on the ORM objects after they are loaded. This presents an incredibly poor user experience for the developer. When they inspect these objects they discover a litany of irrelevant fields. Further, tools which visualize the data structure include these fields, making the visualization extremely confusing. For more in depth explanation of my specific implementation you can refer to the stack overflow post: https://stackoverflow.com/questions/60469773/sqlalchemy-polymorphic-pattern-creates-duplicated-columns

Does anyone know how to prevent SQL alchemy from including fields from sister objects when implementing this pattern?


Mike Bayer

unread,
Mar 3, 2020, 4:38:42 PM3/3/20
to noreply-spamdigest via sqlalchemy

The attributes aren't available from the class that they aren't a part of:

(Pdb) ld = LocationDetail()
(Pdb) hasattr(ld, "starts_at")
False
(Pdb) ld.starts_at
*** AttributeError: Concrete mapped class LocationDetail->LocationDetail does not implement attribute 'starts_at' at the instance level.  Add this property explicitly to mapped class LocationDetail->LocationDetail.

the fact that the attributes are there at all is an artifact of the way AbstractConcreteBase works.  

There is a way to exclude these properties from the base, which is to use the include_properties collection:

class EventDetail(Base, AbstractConcreteBase):

    uid = Column(Integer, primary_key=True)

    @declared_attr
    def event_id(cls):
        return Column(Integer, ForeignKey(Event.uid))

    @declared_attr
    def event(cls):
        return relationship(Event, back_populates="details")

    __mapper_args__ = {
        "include_properties": ["uid", "event_id", "type"]
    }

However, this means that the querying the base EventDetail doesn't actually *load* those properties; when you access them, an additional SQL query has to be emitted.   Even worse, they are actually in the original SELECT query but they aren't assigned to the objects.   I don't know why this is, as the more well-maintained forms of inheritance (joined and single)  don't have any of these issues.   These can all be considered to be bugs with concrete inheritance but I don't have any solution for them right now.

(also make sure you set up back_populates on your mutually-dependent relationships)

Since concrete inheritance is documented as being very buggy (see dragon at https://docs.sqlalchemy.org/en/13/orm/inheritance.html#concrete-inheritance) I would probably just make EventDetail into a non-mapped mixin class and then have LocationDetail and SchedulingDetail load from separate relationships.     
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Mike Bayer

unread,
Mar 3, 2020, 5:00:34 PM3/3/20
to noreply-spamdigest via sqlalchemy
Here's a workaround that tries to adjust for AbstractConcreteBase. The issue is that ABC does not actually create a "with_polymorphic" selectable that includes subclass columns, it maps the base class directly to the full selectable so all those extra columns get mapped.  This can probably be improved, and this issue shouldn't be present with the ConcreteBase class.   By limiting the properties on the local mapper and setting a separate selectable for the load, both issues are solved, however I haven't tested this robustly.  If it works very well for you we can make this a new feature of ABC, however there would need to be logic to figure out the correct "include_properties":

class EventDetail(Base, AbstractConcreteBase):

    uid = Column(Integer, primary_key=True)

    @declared_attr
    def event_id(cls):
        return Column(Integer, ForeignKey(Event.uid))

    @declared_attr
    def event(cls):
        return relationship(Event, back_populates="details")

    __mapper_args__ = {
        "include_properties": ["uid", "event_id", "type"],
    }

    @classmethod
    def __declare_last__(cls):
        cls.__mapper__.with_polymorphic = ("*", cls.__mapper__.local_table)

Isaac Martin

unread,
Mar 4, 2020, 2:56:31 AM3/4/20
to sqlalchemy
Thank you so much for your guidance here. I'm going to give this a shot!
To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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 sqlal...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages