Re: [sqlalchemy] Creating column SQLAlchemy property on parent class based on child column property
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.
On Wed, Feb 3, 2021, at 10:14 AM, Simon King wrote:
> 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
>
> On Wed, Feb 3, 2021 at 10:08 AM Mehrdad Pedramfar
> wrote:
> >
> > 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?
> >
> > stackoverflow link:
> > https://stackoverflow.com/questions/66024558/creating-column-sqlalchemy-property-on-parent-class-based-on-child-column-proper
> >
> > --
> > 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+unsubscr...@googlegroups.com.
> > To view this
Re: [sqlalchemy] Creating column SQLAlchemy property on parent class based on child column property
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
On Wed, Feb 3, 2021 at 10:08 AM Mehrdad Pedramfar
wrote:
>
> 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?
>
> stackoverflow link:
> https://stackoverflow.com/questions/66024558/creating-column-sqlalchemy-property-on-parent-class-based-on-child-column-proper
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/1310342569.2406235.1612346870964%40mail.yahoo.com.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdFiYC-AotO5ZNcNFLGfnYXEHmJpHB%3DWg3hQ0mCXHKu5g%40mail.gmail.com.
[sqlalchemy] Creating column SQLAlchemy property on parent class based on child column property
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?
stackoverflow link:
https://stackoverflow.com/questions/66024558/creating-column-sqlalchemy-property-on-parent-class-based-on-child-column-proper
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1310342569.2406235.1612346870964%40mail.yahoo.com.
