[sqlalchemy] Re: subquery and inheritance
On Apr 30, 2008, at 1:48 AM, kris wrote: 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: Is this functionality available currently or am I waiting for sqlalchemy 0.5? 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. Is there a branch (or trunk) that I should be using 0.5 is soon to be moved to trunk but is currently in the user defined state branch at http://svn.sqlalchemy.org/sqlalchemy/branches/user_defined_state . but yes being able to send column expressions into session.query() is a totally new thing. in the UDS branch, I made some major changes to adjust for what you're trying to do. Over there, if you create a Query using only the columns bound to a table, i.e. like mytable.c.somecolumn, no clause adaption occurs and no mapper definitions affect those expressions, meaning you can in fact hand-create joins across the individual tables in the inheritance setup regardless of how the inheriting mappers were configured (i.e. even if you told your inheriting mappers to by default load from a UNION). Only class-bound properties, ie. MyClass.somecolumn, are subject to the ORM rules. So in effect the Query can now act as a pure pass-through to select(). --~--~-~--~~~---~--~~ 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
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: Is this functionality available currently or am I waiting for sqlalchemy 0.5? If I try the above constructs I am getting AttributeError: 'PGCompiler' object has no attribute 'mapped_table' 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. Is there a branch (or trunk) that I should be using Thanks, Kris --~--~-~--~~~---~--~~ 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 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: subquery and inheritance
On Apr 17, 2008, at 10:42 PM, kris wrote: I am building a tree structure of D1 and D2 nodes.. I am progressively generating a query as before execution using subqueries. s = session.query(D1).filter (...)._values(D1.c.id).statement ... q = session.query (D2).select_from (s).filter (s.base_id == D2.parent_id) s is going to select columns for D1. q is going to select columns for D2. So the above manuever doesnt make much sense as s will not supply the columns which D2 needs. a select_from() call is intended to provide a selectable which provides the same table columns as a base query against D2 would use. The mapper isnt going to guess that the given selectable sort of corresponds to a particular base table (and in this case it doesn't even do that exactly since D1's columns are involved). print q SELECT anon_1.base_id AS anon_1_base_id FROM (SELECT base.id AS base_id FROM base JOIN derived1 ON base.id = derived1.id WHERE ) AS anon_1, base, derived2 WHERE anon_1.base_id = derived2.parent_id ORDER BY anon_1.base_id This seems to generating extra join expression with 'base' without the filtering right, the Query has no idea what you're trying to do and adds in derived2 to the FROM clause since it is just adding those columns into the columns clause (whose table then shows up in the FROM). Any help appreciated. write out the exact SQL statement from which you'd like to select rows from, either D1 or D2 rows or both, and we'll go from there. --~--~-~--~~~---~--~~ 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
The problem stems from a tree structure and creating self joins
on a very large base table.. I am trying to create datasets
of items and filter on the contents of datasets in single query
that it built up progressively.
base = Table ('base',
Column('id', Integer, primarykey=True)
Column('owner', String(255))
...
)
dataset = Table('dataset',
Column('id', Integer, ForiegnKey('base.id'),
primarykey=True)
Column('parent_id', Integer, ForiegnKey('base.id'))
Column('something_id', Integer, ForiegnKey('base.id'))
)
tags = Table('tags',
Column('id', Integer, ForiegnKey('base.id'),
primarykey=True)
Column('parent_id', Integer, ForiegnKey('base.id'))
Column('name', Text))
item = Table('item',
Column('id', Integer, ForiegnKey('base.id'),
primarykey=True)
Column('parent_id', Integer, ForiegnKey('base.id'))
Column('stuff', Text))
mapper(Dataset, dataset, inherits = base)
...
# Find a dataset owned by me
s = session.query(Dataset).filter(Dataset.owner ==me)
# Filter it so that it is tagged with good
s = s.query (and_(tag.name == good, tag.parent_id = Dataset.id))
# Find all somethings in the above dataset
s = s._value(Dataset.c.something_id).statement
# The something are items with tags
# Find those items in the datasets (i.e. all that join w base)
q = session.query(Item).select_from (s)
q = q.filter (and_(tag.c.name==really, tag.parent_id ==
dataset.c.id))
print q.all()
I think I want something like the following:
select item.id
from item,
(select dataset.something_id from base, dataset
where base.id = dataset.id and base.owner ='me'
tag.c.name=good and tag.c.parent_id == base.id
) as datasetsomething
where item.id = datasetsomthing.id
and tag.c.name=good and tag.c.parent_id == item.id
or even further as I think that subqueries are better
performing that massive self joins even when doing simple filtering:
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
Thanks,
kris
--~--~-~--~~~---~--~~
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: I think I want something like the following: select item.id from item, (select dataset.something_id from base, dataset where base.id = dataset.id and base.owner ='me' tag.c.name=good and tag.c.parent_id == base.id ) as datasetsomething where item.id = datasetsomthing.id and tag.c.name=good and tag.c.parent_id == item.id or even further as I think that subqueries are better performing that massive self joins even when doing simple filtering: 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 what I notice about both of these are that you're using correlations. So right off , using the Query, which has opinions about how to build select statements, to build up a statement like this with its current functionality (as well as what we're planning to do in 0.5) is awkward if not impossible. The Query thinks in terms of entities, such as Dataset or Item - when it sees that one of those is desired, it's going to use the full selectable for each of those entities, such as dataset join base or item join base for example.The Query is meant for when you want to think more in terms of objects and not about statement optimization, nor the specifics of joining together the individual tables used to load an inheriting class. The from_self() use case, which is essentially what you're playing with, is a new feature we have which provides a way to select from the results of a query, but is still not as flexible as raw select() constructs. To build up a select() statement at a fine grained level like this is more appropriate using the select() construct directly. You can then feed this construct into the Query using query.from_statement() which will load a distinct entity from each row (or alternatively you can configure it to load multiple entities horizontally from each row). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
