On Mon, Oct 2, 2017 at 7:18 AM, Michael Williamson <[email protected]> wrote: > I'm trying to select a value using a correlated subquery that uses table > inheritance, but the generated query seems to leak the table from the > subquery into the outer query. For instance, suppose employee is a table, > engineer inherits from employee, and each employee has a foreign key to a > country. I want to get the number of engineers per country, so I write:
you found a bug, and it's in a method that has caused lots of bugs so this is not too surprising. https://bitbucket.org/zzzeek/sqlalchemy/issues/4103/_adjust_for_single_inheritance-screwing-up is added. > > engineer_count = Query([func.count(Engineer.id)]) \ > .select_from(Engineer) \ > .filter(Engineer.country_id == Country.id) \ > .correlate(Country) \ > .subquery() \ > .as_scalar() > > print(session.query(Country.id, engineer_count).all()) > > But this generates the query (note that employee is in the outer FROM > clauses): > > SELECT country.id AS country_id, (SELECT count(employee.id) AS count_1 > FROM employee > WHERE employee.country_id = country.id AND employee.type IN (?)) AS anon_1 > FROM country, employee > WHERE employee.type IN (?) > > whereas I'd expect: > > SELECT country.id AS country_id, (SELECT count(employee.id) AS count_1 > FROM employee > WHERE employee.country_id = country.id AND employee.type IN (?)) AS anon_1 > FROM country > > Am I doing something wrong? Full example below. > > Thanks > > Michael > > from sqlalchemy import Column, create_engine, ForeignKey, func, Integer, > String > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import Query, relationship, Session > > > Base = declarative_base() > > > class Country(Base): > __tablename__ = "country" > > id = Column(Integer, primary_key=True) > name = Column(String, nullable=False) > > > class Employee(Base): > __tablename__ = "employee" > > id = Column(Integer, primary_key=True) > discriminator = Column("type", String(50), nullable=False) > country_id = Column(Integer, ForeignKey("country.id"), nullable=False) > country = relationship(Country) > > __mapper_args__ = {"polymorphic_on": discriminator} > > > class Engineer(Employee): > __mapper_args__ = {"polymorphic_identity": "engineer"} > > > engine = create_engine("sqlite://", echo=True) > > Base.metadata.create_all(engine) > > > session = Session(engine) > > session.add(Country(name="United Kingdom")) > > > engineer_count = Query([func.count(Engineer.id)]) \ > .select_from(Engineer) \ > .filter(Engineer.country_id == Country.id) \ > .correlate(Country) \ > .subquery() \ > .as_scalar() > > print(session.query(Country.id, engineer_count).all()) > > > engineer_count = Query([func.count(Employee.id)]) \ > .select_from(Employee) \ > .filter(Employee.discriminator == > Engineer.__mapper_args__["polymorphic_identity"]) \ > .filter(Employee.country_id == Country.id) \ > .correlate(Country) \ > .subquery() \ > .as_scalar() > > print(session.query(Country.id, engineer_count).all()) > > -- > 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.
