Creating column SQLAlchemy property on parent class based on child column property

162 views
Skip to first unread message

Mehrdad Pedramfar

unread,
Feb 3, 2021, 5:08:02 AM2/3/21
to Sqlalchemy
The structure I have created is like below:


    class Parent(Base):
        __tablename__ = 'parent'
    
        id = Field(
            Integer,
            primary_key=True
        )
    
        type_ = Field(
            String(50),
            readonly=True
        )
    
        __mapper_args__ = {
            'polymorphic_on': type_,
        }
    
    class ChildOne(Parent):
        __tablename__ = 'child_one'
        __mapper_args__ = {
            'polymorphic_identity': 'child_one'
        }
    
        id = Field(
            Integer,
            ForeignKey('parent.id'),
            primary_key=True
        )
    
        requested_type_one = Column(
            Integer,
            nullable=False,
        )
        delivered_type_one = Column(
            Integer,
            nullable=False,
        )
        is_done = column_property(
            requested_type_one == delivered_type_one
        )
    
    class ChildTwo(Parent):
        __tablename__ = 'child_two'
        __mapper_args__ = {
            'polymorphic_identity': 'child_two'
        }
    
        id = Field(
            Integer,
            ForeignKey('parent.id'),
            primary_key=True
        )
    
        requested_type_two = Column(
            Integer,
            nullable=False,
        )
        delivered_type_two = Column(
            Integer,
            nullable=False,
        )
        is_done = column_property(
            requested_type_two == delivered_type_two
        )


What I am looking for is to execute ORM query like this:

    session.query(Parent).filter(Parent.is_done.is_(True)).all()

which raises `Parent class does not have is_done` error.

I want  that parent class gets Child class's `is_done` based on different types of child classes, I have tried to created `is_done` as `column_property` on parent but I couldn't make it work. Also I tried using `hybrid_property` and neither it is.

What should I do to make Parent class get `is_done` from its children?


Simon King

unread,
Feb 3, 2021, 10:14:54 AM2/3/21
to sqlal...@googlegroups.com
I don't think you're going to find a way to do that built in to SQLAlchemy.

When you write "session.query(Parent)", SQLAlchemy constructs a query
against the "parent" table. But to filter by your "is_done" property,
it would suddenly need to join every child table into the query and
construct a complicated WHERE clause along the lines of "(type_ =
'child_one' AND child_one.requested_type_one =
child_one.delivered_type_one) OR (type_ = 'child_two' AND
child_two.requested_type_two = child_one.delivered_type_two)".

In theory, I think you *could* implement this as a hybrid property on
Parent, where the "expression" part of the hybrid property constructs
a subquery with a union or a join of all the child tables. It'll be
pretty messy, and might not perform particularly well.

Simon
> --
> 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/1310342569.2406235.1612346870964%40mail.yahoo.com.

Mike Bayer

unread,
Feb 3, 2021, 12:53:41 PM2/3/21
to noreply-spamdigest via sqlalchemy
I think what the OP would have to do is write a UNION, that is:

s.query(Child1).filter(Child1.thing == "thing").union(
   s.query(Child2).filter(Child2.thing == "thing")
)

that's how this problem is approached in SQL.    With SQLAlchemy, any problem should be solved by considering the desired structure in SQL first.
Reply all
Reply to author
Forward
0 new messages