On Apr 18, 2008, at 10:42 PM, kris wrote:
>
> select *
> from nodes,
> (select node2_id as id2
> from assoc,
> (select node2_id as id1
> from assoc where relation = 'is-a'
> and node1_id = 100)
> as isa
> where relation = 'has-a' and node1_id = isa.id
> ) as hasa
> where nodes.id = hasa.id2
>
The example below generates pretty much the exact same query using 0.5
code, which is currently in the query_columns branch. We can also
add a "correlate()" feature to Query for the previous example you
had....its possible we can make that one work as well:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
nodes = Table ('node',metadata,
Column ('id', Integer, primary_key=True),
Column ('name', Text))
assoc = Table ('assoc', metadata,
Column ('node1_id', Integer , ForeignKey('node.id')),
Column ('node2_id', Integer , ForeignKey('node.id')),
Column ('relation', Text)
)
class Node(object):
pass
class Assoc(object):
pass
mapper (Node, nodes, properties={
'nodes':relation(Assoc, primaryjoin=nodes.c.id==assoc.c.node1_id,
backref='left_assoc')
})
mapper(Assoc, assoc, properties={
'node':relation(Node, primaryjoin=assoc.c.node2_id==nodes.c.id,
backref='right_assoc')
}, primary_key=[assoc.c.node1_id, assoc.c.node2_id])
sess = create_session()
subq =
sess.query(Assoc.node2_id.label('id')).filter(Assoc.relation=='is-
a').filter(Assoc.node1_id==100).statement.alias('isa')
subq =
sess
.query
(Assoc
.node2_id
.label
('id')).filter(subq.c.id==Assoc.node1_id).filter(Assoc.relation=='has-
a').statement.alias('hasa')
print sess.query(Node).filter(Node.id==subq.c.id).statement
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
