Consider the following configuration: class Employee(Base): > __tablename__ = "employee" > id = Column(Integer, primary_key=True) > type = Column(String(100)) > cars = relationship("Car") > __mapper_args__ = { > "polymorphic_on": type, > } > > class Car(Base): > __tablename__ = "car" > id = Column(Integer, primary_key=True) > employee_id = Column(Integer, ForeignKey(Employee.id)) > > class Engineer(Employee): > __tablename__ = "engineer" > id = Column(Integer, ForeignKey(Employee.id), primary_key=True) > specialty = Column(String(100)) > __mapper_args__ = { > "polymorphic_identity": "engineer", > }
And the following query: session.add(Engineer()) > session.flush() > E = with_polymorphic(Employee, [Engineer]) > > session.query(E).options(subqueryload(E.cars)).order_by(Engineer.specialty).first() This results in the following exception: Traceback (most recent call last): > File "test.py", line 50, in main > .order_by(Engineer.specialty).first() > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line > 2334, in first > ret = list(self[0:1]) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line > 2201, in __getitem__ > return list(res) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line > 72, in instances > rows = [process[0](row, None) for row in fetch] > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line > 350, in _instance > return _instance(row, result) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line > 455, in _instance > populate_state(state, dict_, row, isnew, only_load_props) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line > 305, in populate_state > populator(state, dict_, row) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", > line 1004, in load_collection_from_subq > () > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", > line 951, in get > self._load() > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", > line 959, in _load > lambda x: x[1:] > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line > 2405, in __iter__ > return self._execute_and_instances(context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line > 2420, in _execute_and_instances > result = conn.execute(querycontext.statement, self._params) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line > 727, in execute > return meth(self, multiparams, params) > File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line > 322, in _execute_on_connection > return connection._execute_clauseelement(self, multiparams, params) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line > 824, in _execute_clauseelement > compiled_sql, distilled_params > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line > 954, in _execute_context > context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line > 1116, in _handle_dbapi_exception > exc_info > File "/usr/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line > 189, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line > 947, in _execute_context > context) > File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", > line 435, in do_execute > cursor.execute(statement, parameters) > sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM-clause > entry for table "engineer" > LINE 3: FROM employee ORDER BY engineer.specialty > ^ > 'SELECT car.id AS car_id, car.employee_id AS car_employee_id, > anon_1.employee_id AS anon_1_employee_id \nFROM (SELECT employee.id AS > employee_id \nFROM employee ORDER BY engineer.specialty \n LIMIT > %(param_1)s) AS anon_1 JOIN car ON anon_1.employee_id = car.employee_id > ORDER BY anon_1.employee_id' {'param_1': 1} It looks like SQLAlchemy cannot figure out that the subquery needs to include a join to engineer. Is there some magical incantation that would make it work? Curiously, a many-to-one relationship works: class Car(Base): > __tablename__ = "car" > id = Column(Integer, primary_key=True) > > class Employee(Base): > __tablename__ = "employee" > id = Column(Integer, primary_key=True) > type = Column(String(100)) > car_id = Column(Integer, ForeignKey(Car.id)) > car = relationship(Car) > __mapper_args__ = { > "polymorphic_on": type, > } > > class Engineer(Employee): > __tablename__ = "engineer" > id = Column(Integer, ForeignKey(Employee.id), primary_key=True) > specialty = Column(String(100)) > __mapper_args__ = { > "polymorphic_identity": "engineer", > } > session.add(Engineer()) > session.flush() > E = with_polymorphic(Employee, [Engineer]) > > session.query(E).options(subqueryload(E.car)).order_by(Engineer.specialty).first() The compiled query: SELECT car.id AS car_id, anon_1.employee_car_id AS anon_1_employee_car_id > FROM (SELECT DISTINCT employee.car_id AS employee_car_id, > engineer.specialty AS engineer_specialty > FROM employee, engineer ORDER BY engineer.specialty > LIMIT %(param_1)s) AS anon_1 JOIN car ON car.id = anon_1.employee_car_id > ORDER BY anon_1.employee_car_id > {'param_1': 1} -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.