this is a bug, for which the "correlate()" workaround I gave you can
be used for now. The fix is tracked from
https://github.com/sqlalchemy/sqlalchemy/issues/4537 and will be in
release 1.3.2.
On Sun, Mar 10, 2019 at 10:32 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
>
> 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.
