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 "something"s 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
-~----------~----~----~----~------~----~------~--~---
