Hi everyone,
I'm trying to do a query where I joinedload with a polymorphic child table.
The child table is using a joined table inheritance scheme. I'm using
with_polymorphic = '*' to accomplish the eager loading of polymorphic types.
The query that SQLAlchemy generates results in a sub select that returns all
the child rows. Here's the setup:
class Parent(Model):
__tablename__ = "parent_table"
id = Column(Integer, primary_key=True)
class ChildBase(Model):
__tablename__ = "child_base_table"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("%s.id" % Parent.__tablename__))
parent = sqlalchemy.orm.relation(Parent, backref='children')
child_type = Column(Integer)
__mapper_args__ = {'polymorphic_on': child_type, 'with_polymorphic':
'*'}
class Child1(ChildBase):
__tablename__ = "child1_table"
__mapper_args__ = {'polymorphic_identity': 1 }
child_id = Column(Integer, ForeignKey("%s.id" %
ChildBase.__tablename__), primary_key=True)
child1_data = Column(Integer)
class Child2(ChildBase):
__tablename__ = "child2_table"
__mapper_args__ = {'polymorphic_identity': 2 }
child_id = Column(Integer, ForeignKey("%s.id" %
ChildBase.__tablename__), primary_key=True)
child2_data = Column(Integer)
Here's the query that I'm doing:
session.query(models.Parent).filter_by(id=999999).options(sqlalchemy.orm.joinedload_all('children'))
Here's the resulting SQL query:
SELECT parent_table.id AS parent_table_id,
anon_1.child_base_table_id AS anon_1_child_base_table_id,
anon_1.child_base_table_parent_id AS
anon_1_child_base_table_parent_id,
anon_1.child_base_table_child_type AS
anon_1_child_base_table_child_type,
anon_1.child1_table_child_id AS anon_1_child1_table_child_id,
anon_1.child1_table_child1_data AS
anon_1_child1_table_child1_data,
anon_1.child2_table_child_id AS anon_1_child2_table_child_id,
anon_1.child2_table_child2_data AS anon_1_child2_table_child2_data
FROM parent_table
LEFT OUTER JOIN (SELECT
child_base_table.id AS
child_base_table_id,
child_base_table.parent_id AS
child_base_table_parent_id,
child_base_table.child_type
ASchild_base_table_child_type,
child1_table.child_id
ASchild1_table_child_id,
child1_table.child1_data
ASchild1_table_child1_data,
child2_table.child_id
ASchild2_table_child_id,
child2_table.child2_data
ASchild2_table_child2_data
FROM child_base_table
LEFT OUTER JOIN child1_table
ON child_base_table.id =
child1_table.child_id
LEFT OUTER JOIN child2_table
ON child_base_table.id =
child2_table.child_id)
AS
anon_1
ON parent_table.id = anon_1.child_base_table_parent_id
WHERE parent_table.id = 999999
Note that the inner select has no WHERE clause which results in selecting
all of the child rows. Without the with_polymorphic option, SQLAlchemy
doesn't produce the inner select. Using subquery_load causes the same
problem. Is there a way to prevent this from happening and eager load the
polymorphic tables?
Thanks!
Anthony
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
