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
[email protected]
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
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
