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