On 6/30/14, 6:01 PM, univerio wrote:
> 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?
there's a little bit of a glitch here, however in any case, the ORDER BY
would be from E.Engineer.specialty.
The glitch is that the subq load at the moment seems to need an
additional hint as to what its selecting from:
session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(E.Engineer.specialty).first()
will look into that.
--
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.
