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:
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.
