I'm having a bit of a problem eager loading the parent node of a hierarchical tree-like table (ie, every node has one to many children). If I simply add a "options(eagerload(Asset.Parent))" to my query it works as expected.
However often I need to select a node based on it's attributes as well as the parent's attributes, so do a "join(Asset.Parent).options(contains_eager(Asset.Parent))" which gets me the correct node, however the parent isn't eager loaded, and worse Asset.Parent is the same as the child. Looking at the SQL that SA is generating, the join is correct, but no columns from the joined row are returned. Attached is a test, and the output. Note about the test, the path column of my table has a unique constraint on it, all three of the queries return the same node, but only the first returns the correct node mapped as Asset.Parent. -- David Gardner Pipeline Tools Programmer, "Sid the Science Kid" Jim Henson Creature Shop dgard...@creatureshop.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~----------~----~----~----~------~----~------~--~---
from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.orm.collections import attribute_mapped_collection DB_HOST = 'dbserver' DB_NAME = 'mydb' DB_USER = 'user' DB_PASS = 'password' db_uri = 'postgres://%s:%...@%s/%s'%(DB_USER,DB_PASS,DB_HOST,DB_NAME) db = create_engine (db_uri, pool_size=200,max_overflow=200) metadata = MetaData(db) class Asset(object): pass asset_table=Table('nodehierarchy',metadata, autoload=True) asset_mapper = mapper(Asset, asset_table, properties = { 'Children' : relation(Asset, cascade='all', primaryjoin=(asset_table.c.uid==asset_table.c.parentuid), collection_class=attribute_mapped_collection('name'), backref=backref('Parent',remote_side=[asset_table.c.uid]), remote_side=[asset_table.c.parentuid]), }) session=create_session() p='testshow/eps/201' db.echo=True a=session.query(Asset).\ options(eagerload(Asset.Parent)).\ filter(Asset.path==p).one() parent_name1=a.Parent.name session.close() session=create_session() a=session.query(Asset).join(Asset.Parent,aliased=True).\ options(contains_eager(Asset.Parent)).\ filter(Asset.name==parent_name1).reset_joinpoint().\ filter(Asset.path==p).one() parent_name2=a.Parent.name session.close() session=create_session() parent_alias=aliased(Asset) a = session.query(Asset).join((parent_alias,Asset.Parent)).\ options(contains_eager(Asset.Parent)).\ filter(parent_alias.name==parent_name1).\ filter(Asset.path==p).one() print parent_name1==parent_name2 print parent_name1==a.Parent.name print parent_name2==a.Parent.name
2009-04-29 09:42:35,575 INFO sqlalchemy.engine.base.Engine.0x...cc10 SELECT anon_1.nodehierarchy_uid AS anon_1_nodehierarchy_uid, anon_1.nodehierarchy_updated AS anon_1_nodehierarchy_updated, anon_1.nodehierarchy_name AS anon_1_nodehierarchy_name, anon_1.nodehierarchy_type AS anon_1_nodehierarchy_type, anon_1.nodehierarchy_parentuid AS anon_1_nodehierarchy_parentuid, anon_1.nodehierarchy_path AS anon_1_nodehierarchy_path, nodehierarchy_1.uid AS nodehierarchy_1_uid, nodehierarchy_1.updated AS nodehierarchy_1_updated, nodehierarchy_1.name AS nodehierarchy_1_name, nodehierarchy_1.type AS nodehierarchy_1_type, nodehierarchy_1.parentuid AS nodehierarchy_1_parentuid, nodehierarchy_1.path AS nodehierarchy_1_path FROM (SELECT nodehierarchy.uid AS nodehierarchy_uid, nodehierarchy.updated AS nodehierarchy_updated, nodehierarchy.name AS nodehierarchy_name, nodehierarchy.type AS nodehierarchy_type, nodehierarchy.parentuid AS nodehierarchy_parentuid, nodehierarchy.path AS nodehierarchy_path FROM nodehierarchy WHERE nodehierarchy.path = %(path_1)s LIMIT 2 OFFSET 0) AS anon_1 LEFT OUTER JOIN nodehierarchy AS nodehierarchy_1 ON nodehierarchy_1.uid = anon_1.nodehierarchy_parentuid 2009-04-29 09:42:35,576 INFO sqlalchemy.engine.base.Engine.0x...cc10 {'path_1': 'testshow/eps/201'} 2009-04-29 09:42:35,588 INFO sqlalchemy.engine.base.Engine.0x...cc10 SELECT nodehierarchy.uid AS nodehierarchy_uid, nodehierarchy.updated AS nodehierarchy_updated, nodehierarchy.name AS nodehierarchy_name, nodehierarchy.type AS nodehierarchy_type, nodehierarchy.parentuid AS nodehierarchy_parentuid, nodehierarchy.path AS nodehierarchy_path FROM nodehierarchy JOIN nodehierarchy AS nodehierarchy_1 ON nodehierarchy_1.uid = nodehierarchy.parentuid WHERE nodehierarchy_1.name = %(name_1)s AND nodehierarchy.path = %(path_1)s LIMIT 2 OFFSET 0 2009-04-29 09:42:35,589 INFO sqlalchemy.engine.base.Engine.0x...cc10 {'name_1': 'eps', 'path_1': 'testshow/eps/201'} 2009-04-29 09:42:35,601 INFO sqlalchemy.engine.base.Engine.0x...cc10 SELECT nodehierarchy.uid AS nodehierarchy_uid, nodehierarchy.updated AS nodehierarchy_updated, nodehierarchy.name AS nodehierarchy_name, nodehierarchy.type AS nodehierarchy_type, nodehierarchy.parentuid AS nodehierarchy_parentuid, nodehierarchy.path AS nodehierarchy_path FROM nodehierarchy JOIN nodehierarchy AS nodehierarchy_1 ON nodehierarchy_1.uid = nodehierarchy.parentuid WHERE nodehierarchy_1.name = %(name_1)s AND nodehierarchy.path = %(path_1)s LIMIT 2 OFFSET 0 2009-04-29 09:42:35,601 INFO sqlalchemy.engine.base.Engine.0x...cc10 {'name_1': 'eps', 'path_1': 'testshow/eps/201'} False False True