I have a polymorphic class with a self-referential parent->children relationship. In an ideal world, I'd be able to do:
session.query(Item) \ .options(joinedload('children')) \ .all() and get the info I need, but I'm on mysql, so this isn't doable due to sqlalchemy insisting on using performance-destroying unbounded subqueries even when the db cannot possibly support them. This one issue has caused me more headaches and man-hours than I could possibly describe, as everything we do has to be doublechecked to make sure we're not accidentally asking sqlalchemy to do a join that it can't handle in an efficient manner. If i have a class using tables A and B, what is the easiest way for me to get sqlalchemy to issue a query that looks like: select * from a left outer join b on .... left outerjoin a as a2 on a2.parent_id = a.id left outerjoin b as b2 on b2.id = a2.id and use a contains_eager option to populate 'children'? The sample code below shows the most successful method I've found, which still doesn't completely work (checking parent_id on the child returns None). How do I solve this problem? If I could take the output of session.query(Item).options(joinedload('children')).all() and just eliminate the parens, everything would be great. Is there an internal param for this I could toggle? It's only left joins (I only query against the base class, and with_polymorphic is set on the mapper, because querying against the poly class would produce an inner join, which guarantees sqla will try to make a subquery and destroy the DB again), so stripping the parens shouldn't have any impact aside from a 99.9% reduction in execution time. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy import * from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import declared_attr, has_inherited_table from sqlalchemy.orm import Session, relationship, backref, joinedload, aliased, contains_eager e = create_engine('sqlite:////tmp/test.db', echo=True) Base = declarative_base() Base.metadata = MetaData(e) class Item(Base): __tablename__ = 'items' id = Column(Integer, primary_key=True) item_type = Column(String(50), default='Item') parent_id = Column(Integer, ForeignKey('items.id')) children = relationship('Item', uselist=True, backref=backref('parent', remote_side=id)) @declared_attr def __mapper_args__(cls): if not has_inherited_table(cls): ret = { 'polymorphic_identity': cls.__name__, 'polymorphic_on': 'item_type', 'with_polymorphic': '*', } else: ret = {'polymorphic_identity': cls.__name__} return ret class CustomItem(Item): __tablename__ = 'items_ext' id = Column(Integer, ForeignKey(Item.id), primary_key=True) custom_name = Column(Unicode(50)) if __name__ == '__main__': Base.metadata.drop_all() Base.metadata.create_all() session = Session(e) parent = CustomItem() child = CustomItem(parent=parent) session.add(parent) session.add(child) session.commit() session.expunge_all() ''' # what I want to do, but can't due to unbounded subquery destroying everything session.query(Item) \ .options(joinedload('children')) \ .all() SELECT items.id AS items_id, items.item_type AS items_item_type, items.parent_id AS items_parent_id, items2.id AS items2_id, anon_1.items_id AS anon_1_items_id, anon_1.items_item_type AS anon_1_items_item_type, anon_1.items_parent_id AS anon_1_items_parent_id, anon_1.items2_id AS anon_1_items2_id FROM items LEFT OUTER JOIN items2 ON items.id = items2.id LEFT OUTER JOIN (SELECT items.id AS items_id, items.item_type AS items_item_type, items.parent_id AS items_parent_id, items2.id AS items2_id FROM items LEFT OUTER JOIN items2 ON items.id = items2.id) AS anon_1 ON items.id = anon_1.items_parent_id ''' t1 = Item.__table__ t2 = CustomItem.__table__ at1 = aliased(t1) at2 = aliased(t2) # this almost works, but parent_id is None items = session.query(Item) \ .outerjoin((t1, 'children'), aliased=True) \ .outerjoin(at2) \ .options(contains_eager('children', alias=at2)) \ .filter(Item.id == 1) \ .all() ''' SELECT items_ext_1.id AS items_ext_1_id, items.item_type AS items_item_type, items.parent_id AS items_parent_id, items_ext_1.custom_name AS items_ext_1_custom_name, items.id AS items_id, items_ext.id AS items_ext_id, items_ext.custom_name AS items_ext_custom_name FROM items LEFT OUTER JOIN items_ext ON items.id = items_ext.id LEFT OUTER JOIN items AS items_1 ON items.id = items_1.parent_id LEFT OUTER JOIN items_ext AS items_ext_1 ON items_1.id = items_ext_1.id WHERE items.id = ? ''' for item in items: print 'ID:', item.id, '\n' print 'children:' for c in item.children: print '\tid=%s, parent_id=%s' % (c.id, c.parent_id)