On Sat, Mar 9, 2019 at 10:12 PM <jay.c...@freenome.com> wrote:
>
> Ah my bad! I really didn't do good testing. Your reply prompted me to find
> the root of the problem and solution though. Posting it here just in case
> this is useful to anyone else!
>
> A gist of the problem I actually had was that I has a column_property in
> Common defined in terms of the Superclass, but really needed to be defined on
> the polymorphic alias of the superclass instead. In full:
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy import String
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import contains_eager
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
> from sqlalchemy.orm import with_polymorphic
> from sqlalchemy.orm import column_property
> from sqlalchemy import select, func, and_, event
>
> Base = declarative_base()
>
>
> class Superclass(Base):
> __tablename__ = "s1"
> id = Column(Integer, primary_key=True)
> common_id = Column(ForeignKey("c.id"))
> common_relationship = relationship("Common", uselist=False,
> innerjoin=True, lazy='noload')
> discriminator_field = Column(String)
> __mapper_args__ = {
> "polymorphic_identity": "superclass",
> "polymorphic_on": discriminator_field,
> }
>
>
> class Subclass(Superclass):
> __tablename__ = "s2"
> id = Column(ForeignKey("s1.id"), primary_key=True)
> __mapper_args__ = {"polymorphic_identity": "subclass"}
>
> class Common(Base):
> __tablename__ = "c"
> id = Column(Integer, primary_key=True)
> num_superclass = column_property(
> select([func.count(Superclass.id)]).where(Superclass.common_id ==
> id).correlate_except(Superclass)
> )
>
> e = create_engine("postgresql://jaychia:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
> s.add(Subclass(common_relationship=Common()))
> s.commit()
>
> poly = with_polymorphic(Superclass, "*")
> s.query(poly).options(contains_eager(poly.common_relationship)).join(
> poly.common_relationship
> ).filter(Common.id == 1).all()
>
> The code above throws an error on the num_superclass column. Fixing the
> num_superclass column to instead use the polymorphic version of Superclass
> fixed it ! So the definition of Common should look like this instead:
>
> class Common(Base):
> __tablename__ = "c"
> id = Column(Integer, primary_key=True)
>
> poly = with_polymorphic(Superclass, "*")
> Common.num_superclass = column_property(
> select([func.count(poly.id)]).where(poly.common_id ==
> Common.id).correlate_except(poly)
> )
>
> Am I doing this right? Seems like this is the best possible fix.
This is really weird and is probably a bug. Send it as correlate()
for now which seems to do the right thing:
class Common(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
Common.num_superclass = column_property(
select([func.count(Superclass.id)])
.where(Superclass.common_id == Common.id)
.correlate(Common)
)
>
> On Friday, March 8, 2019 at 8:53:35 PM UTC-8, Mike Bayer wrote:
>>
>> On Fri, Mar 8, 2019 at 3:56 PM <jay....@freenome.com> wrote:
>> >
>> > Hi everyone!
>> >
>> > I am trying to use the contains_eager option on a simple polymorphic query
>> > that looks like:
>> >
>> > class Superclass():
>> > common_relationship = relationship('Common', ...)
>> > discriminator_field = Column(String...)
>> > __mapper_args__ = {'polymorphic_identity': 'superclass',
>> > 'polymorphic_on': discriminator_field}
>> >
>> > class Subclass(Superclass):
>> > fields...
>> >
>> > poly = with_polymorphic(Superclass, '*')
>> > db.session.query(poly).options(contains_eager(poly.common_relationship)).join(poly.common_relationship).filter(Common.id
>> > == 1).all()
>> >
>> > This code throws an error when I execute the query:
>> >
>> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) subquery uses
>> > ungrouped column "common.id" from outer query
>>
>> this example is very vague and does not indicate what you are doing in
>> full. Below is your example turned into a working mapping including
>> your exact query using Postgresql, works fine, and notably does not
>> generate a subquery which would only occur if there was more going on
>> above. Please provide a complete MCVE, most likely by modifying the
>> fully runnable code below, thanks!
>>
>> from sqlalchemy import Column
>> from sqlalchemy import create_engine
>> from sqlalchemy import ForeignKey
>> from sqlalchemy import Integer
>> from sqlalchemy import String
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import contains_eager
>> from sqlalchemy.orm import relationship
>> from sqlalchemy.orm import Session
>> from sqlalchemy.orm import with_polymorphic
>>
>> Base = declarative_base()
>>
>>
>> class Superclass(Base):
>> __tablename__ = "s1"
>>
>> id = Column(Integer, primary_key=True)
>> common_id = Column(ForeignKey("c.id"))
>>
>> common_relationship = relationship("Common")
>> discriminator_field = Column(String)
>> __mapper_args__ = {
>> "polymorphic_identity": "superclass",
>> "polymorphic_on": discriminator_field,
>> }
>>
>>
>> class Subclass(Superclass):
>> __tablename__ = "s2"
>> id = Column(ForeignKey("s1.id"), primary_key=True)
>> __mapper_args__ = {"polymorphic_identity": "subclass"}
>>
>>
>> class Common(Base):
>> __tablename__ = "c"
>> id = Column(Integer, primary_key=True)
>>
>>
>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>> Base.metadata.drop_all(e)
>> Base.metadata.create_all(e)
>>
>> s = Session(e)
>> s.add(Subclass(common_relationship=Common()))
>> s.commit()
>>
>> poly = with_polymorphic(Superclass, "*")
>> s.query(poly).options(contains_eager(poly.common_relationship)).join(
>> poly.common_relationship
>> ).filter(Common.id == 1).all()
>>
>> query at the end:
>>
>> 2019-03-08 23:50:11,847 INFO sqlalchemy.engine.base.Engine SELECT c.id
>> AS c_id, s1.id AS s1_id, s1.common_id AS s1_common_id,
>> s1.discriminator_field AS s1_discriminator_field, s2.id AS s2_id
>> FROM s1 LEFT OUTER JOIN s2 ON s1.id = s2.id JOIN c ON c.id = s1.common_id
>> WHERE c.id = %(id_1)s
>> 2019-03-08 23:50:11,847 INFO sqlalchemy.engine.base.Engine {'id_1': 1}
>>
>>
>>
>>
>> >
>> >
>> > Removing the contains_eager option and replacing it with a lazy or join
>> > option does solve the problem, but introduces overhead to my SQL query.
>> > What I am trying to achieve here is for the query to not perform two joins
>> > (once for the join to filter on, and once for the join to populate the
>> > poly.common_relationship field). Am I approaching this entirely wrong here?
>> >
>> > Thanks in advance!
>> >
>> > Jay
>> >
>> > This e-mail is private and confidential and is for the addressee only. If
>> > misdirected, please notify us by telephone, confirming that it has been
>> > deleted from your system and any hard copies destroyed. You are strictly
>> > prohibited from using, printing, distributing or disseminating it or any
>> > information contained in it save to the intended recipient.
>> >
>> > --
>> > 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 post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
>
> This e-mail is private and confidential and is for the addressee only. If
> misdirected, please notify us by telephone, confirming that it has been
> deleted from your system and any hard copies destroyed. You are strictly
> prohibited from using, printing, distributing or disseminating it or any
> information contained in it save to the intended recipient.
>
> --
> 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
