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 [email protected].
To post to this group, send email to [email protected].
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)
