Hi there,
Using SQLAlchemy 1.0.9
I am dealing with some legacy code and came across the following issue. It
appears that in a polymorphic relationship SQLAlchemy is not able to
correctly determine what to load for a relationship when using a subselect
(due to limit on query) and child class has a different field name for the
primary key.
Here is a reproducible test case to show the issue better:
from uuid import uuid1 as uuid
import sqlalchemy
import sqlalchemy.orm
from sqlalchemy import (
Column, Unicode, ForeignKey, CHAR, Boolean, UniqueConstraint
)
from sqlalchemy.orm import relationship, backref, synonym
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class Context(Base):
'''Represent a context.'''
__tablename__ = 'context'
context_type = Column(Unicode(32), nullable=False)
__mapper_args__ = {
'polymorphic_on': context_type,
'polymorphic_identity': 'context'
}
id = Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))
class Task(Context):
'''Represent a task.'''
__tablename__ = 'task'
__mapper_args__ = {
'polymorphic_identity': 'task'
}
# Change this and references to it to 'id' to fix issue.
task_id = Column(
CHAR(36),
ForeignKey('context.id'),
primary_key=True
)
scopes = relationship('Scope', secondary='task_scope')
class Scope(Base):
'''Represent a Scope.'''
__tablename__ = 'scope'
id = Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))
class TaskScope(Base):
'''Represent a relation between a scope and a task.'''
__tablename__ = 'task_scope'
id = Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))
scope_id = Column(CHAR(36), ForeignKey(Scope.id), nullable=False)
task_id = Column(CHAR(36), ForeignKey(Task.task_id), nullable=False)
def main():
'''Execute test.'''
engine = sqlalchemy.create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
task = Task()
scope = Scope()
session.add(task)
session.add(scope)
session.commit()
link = TaskScope(scope_id=scope.id, task_id=task.task_id)
session.add(link)
session.commit()
query = session.query(Task).options(
sqlalchemy.orm.load_only('context_type'),
sqlalchemy.orm.joinedload('scopes').load_only()
).limit(10)
print query
results = query.all()
print results
if __name__ == '__main__':
main()
Running the above gives:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column:
task.task_id [SQL: u'SELECT anon_1.context_context_type AS
anon_1_context_context_type, anon_1.context_id AS anon_1_context_id,
anon_2.scope_1_id AS scope_1_id \nFROM (SELECT context.context_type AS
context_context_type, context.id AS context_id \nFROM context JOIN task ON
context.id = task.task_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN
(SELECT task_scope_1.id AS task_scope_1_id, task_scope_1.scope_id AS
task_scope_1_scope_id, task_scope_1.task_id AS task_scope_1_task_id,
scope_1.id AS scope_1_id \nFROM task_scope AS task_scope_1 JOIN scope AS
scope_1 ON scope_1.id = task_scope_1.scope_id) AS anon_2 ON task.task_id =
anon_2.task_scope_1_task_id'] [parameters: (10, 0)]
If you change 'task_id' to 'id' (and references to it) then the query will
now work correctly.
As I mentioned this is legacy code so there are many references to task_id
throughout the code base unfortunately. I have found a workaround by using
a synonym, but wanted to report the issue to see if the failure is expected
and there is a requirement for the primary key field names to match or not.
cheers,
Martin
--
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.
