On 6/3/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > - filter_by() would gain a new positional argument, which is > optional, which is either a string or list of strings indicating > property names, indicating a join path. when this argument is > present, a self-contained join among those mapped properties will be > generated, and the keyword criterion of the filter_by() would be > expressed against the endpoint of that join. by "self-contained", i > mean that the tables used in the join will be *aliased* anonymously, > so that different filter_by()s which specify join paths that overlap > can be used together. the existing behavior of being able to say > query.join(['x', 'y', 'z']).filter_by(**kwargs) remains as well (but > is different in that it doesnt create aliases).
... > - the new "positional" argument of filter_by() is based on Gaetan's > idea, as well as some light that was shed on the IRC channel. it > looks like this: > > session.query(User).filter_by(['orders', 'items', 'keywords'], > keyword_name='foo').filter_by(['orders', 'items'], > item_price=49.50).list() > > above, we have created two joins from the "users" table, one of which > joins from 'orders' to 'items' to 'keywords', and the other from > 'orders' to 'items'. the two sets of joins are constructed using > anonymous aliases, so that you get all users who have purchased an > item that has the 'foo' keyword, and have also purchased an item that > costs 49.50. the two paths across 'orders' and 'items' are isolated > from each other. > > if aliases were not used above, the redundant 'orders' and 'items' > tables would converge into one FROM clause each in the final > statement since thats the behavior of select(), but then the > semantics of the query would then be "all users who purchased items > that cost 49.50 and also have the 'foo' keyword". > > the above capability is currently available by constructing your own > Alias object and building join criterion manually. But it was > pointed out on IRC that the Django ORM constructs filtering queries > using aliases as above. for a lot of cases the approach saves a lot > of lines of code spent creating manual aliases. Hi there, I like where things are going, but I think I should clarify how the Django ORM does things to my knowledge. The filter method in the Django ORM only supports keyword args a la filter_by. Every query is based off a mapped object (like Post). Each filter can access other tables' columns via the relations, using a construct like: .filter(jobtitle__content__txt__icontains = keyword, domain__str__content__txt__icontains = keyword) Both jobtitle and domain__str (the domain relation and then the "str" relation) are of a mapped object Strids type, which maps to the strids table. This has a relation content to a table with contains the actual text to use for the current locale. (__icontains is there to perform a LIKE '%keyword%', since there's no ClauseElement equivalent. I'm not suggesting supporting that.) The joined tables are aliased as the path from the base object. In this case, the "jobtitle" join is: INNER JOIN "strids" AS "positionprofiles__jobtitle" ON "positionprofiles"."jobtitleid" = "positionprofiles__jobtitle"."id" The "domain__str__content" join is: INNER JOIN "dimensions" AS "positionprofiles__domain" ON "positionprofiles"."domainid" = "positionprofiles__domain"."id" INNER JOIN "strids" AS "positionprofiles__domain__str" ON "positionprofiles__domain"."strid" = "positionprofiles__domain__str"."id" INNER JOIN "content" AS "positionprofiles__domain__str__content" ON "positionprofiles__domain__str"."id" = "positionprofiles__domain__str__content"."strid" ie, it joins on domain, then str, then content, giving each one an alias based on the path from the mapped object. I'm a little worried that using anonymous aliases will make some queries impossible. For example, if you want to list all purchases of product type B made by a manufacturer with a particular discount agreement, since one would require a filter_by(['product', 'store'], ...) and the other will require a filter_by(['product', 'type'], ...). In the Django ORM, since the path to 'product' is the same, it's the same join. Not being able to rejoin on the same path may make certain kinds of query building impossible. Then again, using anonymous aliases for each kind can solve different types of problems that can't be done without them. I think the principle of least astonishment would be to not have a new join for the exact same path. But, given enough documentation, one can avoid astonishment. I hope this made the situation clearer, and not more confusing. Neil -- Neil Blakey-Milner http://nxsy.org/ [EMAIL PROTECTED] --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
