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
-~----------~----~----~----~------~----~------~--~---
