[sqlalchemy] Re: subquery and inheritance
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
hadits 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: subquery and inheritance
On Apr 18, 2008, at 3:18 PM, kris wrote:
select item.id
from item,
(select dataset_me.something_id
from (select * from base, dataset
where base.id = dataset.id and
base.owner=me)
as dataset_me
where
tag.c.name=good and tag.c.parent_id ==
dataset_me.id
) as datasetsomething
where item.id = datasetsomthing.id
and tag.c.name=good and tag.c.parent_id == item.id
If I add a simple correlate feature to Query in 0.5, you can use the
raw Table object to bypass the ORM meaning of Dataset and Base.
the query above is not quite complete but I can get an approximation
like this:
subq =
sess
.query
(dataset
.c
.id
.label
('id
')).filter
(Base
.owner
=='me').filter(dataset.c.id==Base.id).statement.alias('dataset_me')
subq =
sess
.query
(subq
.c
.id
.label
('id
')).filter
(Tag
.name
=
=
'good
').filter
(Tag
.parent_id
==subq.c.id).correlate(Tag).statement.alias('datasetsomething')
print
sess
.query
(item
.c
.id
).filter
(item
.c
.id
=
=
subq
.c
.id).filter(Tag.name=='good').filter(Tag.parent_id==item.c.id).statement
produces:
SELECT item.id AS item_id
FROM item, (SELECT dataset_me.id AS id
FROM (SELECT dataset.id AS id
FROM dataset, base
WHERE base.owner = :owner_1 AND dataset.id = base.id) AS dataset_me
WHERE tags.name = :name_1 AND tags.parent_id = dataset_me.id) AS
datasetsomething, tags
WHERE item.id = datasetsomething.id AND tags.name = :name_2 AND
tags.parent_id = item.id
So I think going forward, the concept of use the Table objects
directly when you want to bypass the higher level meaning of
Dataset.id, i.e. that its selecting from a join of Dataset and Base,
might be a good way to go with this.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
