On Mon, 2 Oct 2017 10:00:43 -0400 Mike Bayer <[email protected]> wrote:
> 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. Thanks for the quick reply, Mike. For now, we're querying against the base class and adding in the condition on the discriminator manually. When using Query, is there a way to make sure that only tables explicitly listed using `select_from` or `join` are selected from? This would help us prevent cases where we accidentally perform a cross join, whether as a result of bugs or an incorrectly written query. Thanks Michael > > > > 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.
