subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry

[univerio]

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} 

[Mike Bayer]

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.

[Mike Bayer]

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.

[Jack Zhou]

Thanks for the quick response, Mike!

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

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)

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+...@googlegroups.com.