[sqlalchemy] Eager Load of Polymorphic Children Causes Table Scan

[Anthony Tran]

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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.