[sqlalchemy] subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry
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])
Re: [sqlalchemy] subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry
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:
Re: [sqlalchemy] subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry
On 6/30/14, 7:03 PM, Mike Bayer wrote: 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. OK, for now session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(Engineer.specialty).first() is fine (don't need E.Engineer in the order_by), the issue is https://bitbucket.org/zzzeek/sqlalchemy/issue/3106/subquery-load-on-with_polymorphic-entity and with the patch there once I get tests in, the query will work as you had it originally. -- 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.
Re: [sqlalchemy] subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry
Thanks for the quick response, Mike! On Mon, Jun 30, 2014 at 4:26 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 6/30/14, 7:03 PM, Mike Bayer wrote: 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. OK, for now session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(Engineer.specialty).first() is fine (don't need E.Engineer in the order_by), the issue is https://bitbucket.org/zzzeek/sqlalchemy/issue/3106/subquery-load-on-with_polymorphic-entity and with the patch there once I get tests in, the query will work as you had it originally. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gvOJr4cSO-A/unsubscribe. To unsubscribe from this group and all its topics, 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. -- 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.
Re: [sqlalchemy] subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry
On 6/30/14, 7:29 PM, Jack Zhou wrote: Thanks for the quick response, Mike! that's all fixed in master / rel_1_0 and rel_0_9 branches (as you know I like to fix these deep polymorphic loader issues ASAP) On Mon, Jun 30, 2014 at 4:26 PM, Mike Bayer mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com wrote: On 6/30/14, 7:03 PM, Mike Bayer wrote: 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. OK, for now session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(Engineer.specialty).first() is fine (don't need E.Engineer in the order_by), the issue is https://bitbucket.org/zzzeek/sqlalchemy/issue/3106/subquery-load-on-with_polymorphic-entity and with the patch there once I get tests in, the query will work as you had it originally. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gvOJr4cSO-A/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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.
