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.
