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.
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 <javascript:>>
> 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 <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
