On Fri, Mar 8, 2019 at 3:56 PM <[email protected]> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
