Hi David -
One thing I notice is that your "remote_side" on the self referential
relation from TypeNode->Children is not needed, whereas it *is* needed
on the TypeNode->Parent side, which is the many to one side, using
backref=backref('Parent', remote_side=[typehierarchy_table.c.id]).
Without it, SQLA assumes TypeNode->Parent is a one to many collection.
However, I cannot reproduce your behavior - if the remote_side is in
fact missing on the backref side, the comparison of
TypeNode.Parent==something correctly raises this error:
sqlalchemy.exc.InvalidRequestError: Can't compare a collection to an
object or collection; use contains() to test for membership.
Here is a test case illustrating the current 0.5rc3 behavior - you can
see that a many-to-one self referential comparison does not generate
an EXISTS anymore:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
nodes = Table('nodes', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('nodes.id'))
)
class Node(object):
def __init__(self, **kw):
for k in kw:
setattr(self, k, kw[k])
mapper(Node, nodes, properties={
'Children':relation(Node, backref=backref('Parent',
remote_side=nodes.c.id))
})
print
create_session().query(Node).filter(Node.Parent==Node(id=2)).statement
output:
SELECT nodes.id, nodes.parent_id
FROM nodes
WHERE :param_1 = nodes.parent_id
On Nov 10, 2008, at 3:36 PM, David Gardner wrote:
>
> Had a problem this morning where SA 0.5rc3 was returning None, while
> 0.5rc2 and 0.4.8 returned the expected object/row.
>
> tables & mappers:
> ----------------------------------
> typehierarchy_table = Table('typehierarchy', metadata, autoload=True)
> typehierarchy_names_table = Table('typehierarchy_names', metadata,
> autoload=True)
> mapper(TypeNode, typehierarchy_table, properties={
> 'AutoPopNames':relation(TypeAutoPop, backref='TypeNode'),
> 'Children':relation(TypeNode,
> primaryjoin=(typehierarchy_table.c.id==typehierarchy_table.c.parent),
>
> remote_side=[typehierarchy_table.c.parent],backref='Parent')},
> save_on_init=False)
>
> mapper(TypeAutoPop,typehierarchy_names_table, save_on_init=False)
>
>
> 0.5.0rc3:
> ----------------
>>>> from assetdb import *
> DEV BRANCH:assetdb.py
>>>> print sqlalchemy.__version__
> 0.5.0rc3
>>>> session=create_session()
>>>> t='h2_prj'
>>>> show='sid'
>>>> parent=None
>>>> db.echo=True
>>>> tn =
> session
> .query
> (TypeNode
> ).filter
> (TypeNode
> .Parent
> =
> =parent).filter(TypeNode.type==t).filter(TypeNode.project==show).all()
> 2008-11-10 12:19:06,534 INFO sqlalchemy.engine.base.Engine.0x...af50
> SELECT typehierarchy.project AS typehierarchy_project,
> typehierarchy.parent AS typehierarchy_parent, typehierarchy.id AS
> typehierarchy_id, typehierarchy.type AS typehierarchy_type,
> typehierarchy.static AS typehierarchy_static
> FROM typehierarchy
> WHERE NOT (EXISTS (SELECT 1
> FROM typehierarchy AS typehierarchy_1
> WHERE typehierarchy.id = typehierarchy_1.parent)) AND
> typehierarchy.type
> = %(type_1)s AND typehierarchy.project = %(project_1)s
> 2008-11-10 12:19:06,535 INFO sqlalchemy.engine.base.Engine.0x...af50
> {'type_1': 'h2_prj', 'project_1': 'sid'}
>>>> tn
> []
>>>>
>
>
> 0.5.0rc2:
> ----------------
>>>> from assetdb import *
> DEV BRANCH:assetdb.py
>>>> print sqlalchemy.__version__
> 0.5.0rc2
>>>> session=create_session()
>>>> t='h2_prj'
>>>> show='sid'
>>>> parent=None
>>>> db.echo=True
>>>> tn =
> session
> .query
> (TypeNode
> ).filter
> (TypeNode
> .Parent
> =
> =parent).filter(TypeNode.type==t).filter(TypeNode.project==show).all()
> 2008-11-10 12:30:50,452 INFO sqlalchemy.engine.base.Engine.0x...9a10
> SELECT typehierarchy.project AS typehierarchy_project,
> typehierarchy.parent AS typehierarchy_parent, typehierarchy.id AS
> typehierarchy_id, typehierarchy.type AS typehierarchy_type,
> typehierarchy.static AS typehierarchy_static
> FROM typehierarchy
> WHERE NOT (EXISTS (SELECT 1
> FROM typehierarchy
> WHERE typehierarchy.id = typehierarchy.parent)) AND
> typehierarchy.type =
> %(type_1)s AND typehierarchy.project = %(project_1)s
> 2008-11-10 12:30:50,452 INFO sqlalchemy.engine.base.Engine.0x...9a10
> {'type_1': 'h2_prj', 'project_1': 'sid'}
>>>> tn
> [<assetdb.TypeNode object at 0x201f3d0>]
>
>
>
> --
>
> 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 sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
