[sqlalchemy] Re: self-referential table question
Michael, Works perfectly. Thanks much. Steve --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
Steve Zatz wrote: I realize this is actually an SQL question but I haven't been able to figure out the answer. In a simple self-referential table, the following produces all the Nodes that are parents to some child node(s): node_table_alias = node_table.alias() parents = session.query(Node).filter(Node.id == node_table_alias.c.parent_id) I can't figure out the analogous query that produces all the Nodes that are not parents to another node. It is clear that: non_parents = session.query(Node).filter(Node.id != node_table_alias.c.parent_id) doesn't work but I can't figure out what the right query is. Any help would be appreciated. another option is: .query(Node).filter(not_(Node.id.in_(select([Node.parent_id] --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
another option is: .query(Node).filter(not_(Node.id.in_(select([Node.parent_id] jason, thanks for the alternative method. Steve --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
On Jan 27, 2008, at 8:06 PM, Steve Zatz wrote: I realize this is actually an SQL question but I haven't been able to figure out the answer. In a simple self-referential table, the following produces all the Nodes that are parents to some child node(s): node_table_alias = node_table.alias() parents = session.query(Node).filter(Node.id == node_table_alias.c.parent_id) I can't figure out the analogous query that produces all the Nodes that are not parents to another node. It is clear that: non_parents = session.query(Node).filter(Node.id != node_table_alias.c.parent_id) doesn't work but I can't figure out what the right query is. Any help would be appreciated. when you want to find objects who dont have any one-to-many children of some criterion, you use a NOT EXISTS clause, i.e.: select * from parent_table where not exists (select 1 from child_table where child_table.parent_id = parent_table.id) if its specifically nodes that are not parents to a single node, you can just query all nodes whos id is not that child's parent_id: select * from nodes where nodes.id!= (select parent_id from nodes where id=child id) sess.query(Node).filter(Node.id!=(select([Node.parent_id], Node.id==child id))).all() or: select * from nodes where not exists ( select 1 from nodes as children where children.parent_id=nodes.id and children.id=child id) you could *almost* use the any() operator to do this but theres currently no way to get the aliasing behavior into any()..so using exists(): nalias = nodes.alias() sess.query(Node).filter( ~exists([1], and_(nalias.c.parent_id==Node.id, nalias.c.id==child id)) ).all() --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
Works perfectly. Your responsiveness and the usefulness of SQLAlchemy continue to amaze. Thanks. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
Works. Thanks. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
yeah... update to SVN 2124 and set up your mapper like this:
mapper(Item, item_table,
properties = dict(children = relation(Item,
remote_side=[item_table.c.parent_uuid], backref=backref('parent',
remote_side=[item_table.c.uuid])),
))
the remote_side argument will now be used to specify which side of
the relation is remote for a self-referential mapper when you want to
force a many-to-one relationship such as in the case of your parent
backreference. for now, it also is needed for a join condition that
contains no primary key columns.
thats all you need to know going forward. now heres some things that
you dont need to know:
as to why you need the flag for a join that has no primary key columns,
this is because the old logic, which continues to take effect if
remote_side is not present, relies upon primary keys. the old logic
cant rely upon the foreign keys because of the current documented usage
of the foreignkey argument for self-referential tables.
the docs say to use the foreignkey flag to force the many-to-one
self-referential relationship; that usage will be deprecated in favor
of the new remote_side argument which is dedicated to this purpose.
foreignkey will now be used only for its primary purpose, which is to
specify foreign key columns in the case that none are present on the
table metadata.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
