NoSuchColumnError when using a sql statement as the polymorphic_on and loading a relationship via joinedload
Test Case
from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(types.Integer, primary_key=True)
discriminator = Column(types.String(50), nullable=False)
child_id = Column(types.Integer)
child = relationship('A', primaryjoin='foreign(A.child_id) == remote(A.id)')
__mapper_args__ = {
'polymorphic_identity': 'a',
"polymorphic_on": case([
(discriminator == "a", "a"),
], else_="b"),
}
class B(A):
__mapper_args__ = {
'polymorphic_identity': 'b'
}
e = create_engine('sqlite:///:memory:', echo='debug')
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
session = Session(e, autoflush=True, autocommit=False)
session.add_all([
A(id=1, discriminator='a'),
A(id=2, discriminator='b', child_id=1),
A(id=3, discriminator='c', child_id=1),
])
session.commit()
session.query(A).options(joinedload('child')).all()
Error:
2016-09-19 15:03:31,801 INFO sqlalchemy.engine.base.Engine SELECT a.id AS a_id, a.discriminator AS a_discriminator, a.child_id AS a_child_id, CASE WHEN (a.discriminator = ?) THEN ? ELSE ? END AS _sa_polymorphic_on, a_1.id AS a_1_id, a_1.discriminator AS a_1_discriminator, a_1.child_id AS a_1_child_id, CASE WHEN (a_1.discriminator = ?) THEN ? ELSE ? END AS anon_1
FROM a LEFT OUTER JOIN a AS a_1 ON a.child_id = a_1.id
2016-09-19 15:03:31,801 INFO sqlalchemy.engine.base.Engine ('a', 'a', 'b', 'a', 'a', 'b')
2016-09-19 15:03:31,801 DEBUG sqlalchemy.engine.base.Engine Col ('a_id', 'a_discriminator', 'a_child_id', '_sa_polymorphic_on', 'a_1_id', 'a_1_discriminator', 'a_1_child_id', 'anon_1')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (1, u'a', None, u'a', None, None, None, u'b')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (2, u'b', 1, u'b', 1, u'a', None, u'a')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (3, u'c', 1, u'b', 1, u'a', None, u'a')
Traceback (most recent call last):
File "sqla_test.py", line 45, in <module>
session.query(A).options(joinedload('child')).all()
File "env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2613, in all
return list(self)
File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 86, in instances
util.raise_from_cause(err)
File "env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 71, in instances
rows = [proc(row) for row in fetch]
File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 597, in polymorphic_instance
return instance_fn(row)
File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 428, in _instance
loaded_instance, populate_existing, populators)
File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 497, in _populate_full
populator(state, dict_, row)
File "env/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1586, in load_scalar_from_joined_new_row
dict_[key] = _instance(row)
File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 592, in polymorphic_instance
discriminator = row[polymorphic_on]
File "env/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 408, in _key_fallback
expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'CASE WHEN (a_1.discriminator = :param_1) THEN :param_2 ELSE :param_3 END'"
It seems that the _sa_polymorphic_on
column for the joined relationship gets named anon1
and then cannot be found in the row
Comments (9)
-
repo owner -
repo owner - changed milestone to 1.1
-
repo owner okey doke seems like the docs include the case() freestanding in polymorphic_on
-
reporter ah thanks, changing it to a first class mapping seems to work
-
repo owner OK in the bad case:
/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py(265)_setup_entity_query()
(Pdb) context.secondary_columns [Column('id', Integer(), table=<%(140584238025168 a)s>, primary_key=True, nullable=False), Column('discriminator', String(length=50), table=<%(140584238025168 a)s>, nullable=False), Column('child_id', Integer(), table=<%(140584238025168 a)s>), <sqlalchemy.sql.elements.Label object at 0x7fdc51376610>] (Pdb) context.secondary_columns[-1] <sqlalchemy.sql.elements.Label object at 0x7fdc51376610> (Pdb) print context.secondary_columns[-1] CASE WHEN (a_1.discriminator = :param_1) THEN :param_2 ELSE :param_3 END (Pdb) adapter.columns[adapter.mapper.polymorphic_on] <sqlalchemy.sql.elements.Label object at 0x7fdc51360690>
polymorphic_on, when retrieved from the adapter, generates a new object, and is not set to the value in context.secondary_columns. in the good case, it is.
-
repo owner -
repo owner - changed milestone to 1.0.xx
-
repo owner - changed status to resolved
Ensure mapper.polymorphic_on is polymorphic_prop.columns[0]
Fixed bug where joined eager loading would fail for a polymorphically- loaded mapper, where the polymorphic_on was set to an un-mapped expression such as a CASE expression.
Change-Id: Iffe68196aaac592165c89684f09f4c06cd78ce54 Fixes:
#3800(cherry picked from commit 97b294093617eca7298a2fe97bd23bd6dc3b59bf)→ <<cset 3202fb4eabd7>>
-
repo owner Ensure mapper.polymorphic_on is polymorphic_prop.columns[0]
Fixed bug where joined eager loading would fail for a polymorphically- loaded mapper, where the polymorphic_on was set to an un-mapped expression such as a CASE expression.
Change-Id: Iffe68196aaac592165c89684f09f4c06cd78ce54 Fixes:
#3800→ <<cset 97b294093617>>
- Log in to comment
well, do it like this so your expression is a first class mapping:
I need to go back and see what the guidance is on freestanding "case()" for polymorphic_on in the docs. seems to work for all the other loaders including subqueryload.