Michael Bayer wrote: > hey dan - > > not sure if im understanding. we already have join(table1, table2, > onclause), outerjoin(t1, t2, onclause), t1.join(t2, onclause), and > t1.outerjoin(t2, onclause), all of which have "onclause" as optional.
Yes, for Table objects that works fine, but I'm talking about joining mapped classes. Does that make sense? Is it possible to do join(Entity1, Entity2, onclause) where EntityX is a class with a mapper? It certainly would not be acceptable to automatically add join(), outerjoin(), etc. methods to all mapped classes since that would be a serious invasion of user-defined namespaces (SA puts all kinds of private stuff in there, but 'c' and 'mapper' are the only attributes that I know of that are not private that get added to mapped classes). Also, the join path syntax allows on to join mapped classes along specific relationships in a very concise way, even when there are multiple join paths between two objects. That seems like an enhancement to me. > > So i dont see how from_obj=[users.join(ordertable).join(...)] is > ambiguous. I was talking about ambiguous in terms of join type, but I forgot about outerjoin() when I wrote that part... > if you want outerjoins, use users.outerjoin(). if you > want the onclause, specify it. Also I dont understand why trying to > find the relationship between the two args (you mean the "onclause", > right ? ) is error-prone. it looks for a single explicit foreign > key relationship between the two tables or selectables. if theres > not, it throws an error. if theres more than one foreign key > relationship, it throws an error (older versions of SA were not as > intelligent). Ahh, I didn't realize that last part had changed. I thought it used any relationship path it could find if an onclause was not specified. > so I dont know if by "error prone" you meant, "throws > an error", which i think is reasonable behavior, versus "silently > fails to do the right thing", which is not. I agree. > > - mike > > On Nov 11, 2006, at 2:12 PM, Daniel Miller wrote: > >> >> BTW, this post started out as a reply to James but grew into a >> collective summary of my thoughts on the entire thread so far. >> You've been warned :) >> >> James Taylor wrote: >>> But wouldn't the join from orders to items be OUTER in this case >>> since you can have orders with a total > 50 but no items. (Your >>> application logic probably forbids this, but it is allowed at the >>> relational level). >>> >>> Gets complicated quick! >> Yes, that's one of the things I started to dislike as I got more >> into the details. The 'obj1.obj2.obj3' join syntax does not imply >> the type of join (i.e. inner, outer, cross, etc.). My initial >> thought is to say that this type of join syntax would always use >> inner joins, which is easy enough to remember. >> >> One thing I'm not quite clear on is how the join type is specified >> with the other join syntaxes in SA. There's join_to/join_via and >> Mike talked about from_obj=[users.join(ordertable).join(...)], but >> both of those are just as ambiguous as obj1.obj2.obj3 when it comes >> to the join type (and they're a lot more verbose at that). I guess >> SelectResults has outerjoin_to()...more on that later. >> >> Here is a more complete join syntax proposal: >> >> INNER JOIN (join path syntax sugar): >> User.c.orders >> User.c.orders.items >> >> INNER JOIN (non-sugar): >> join(User.c.orders) >> join(User.c.orders.items) >> join(User, Order, User.c.user_id > Order.c.user_id) >> >> OUTER JOIN: >> outerjoin(User.c.orders) >> outerjoin(User.c.orders.items) >> outerjoin(User, Order, User.c.user_id > Order.c.user_id) >> >> RANDOM JOIN: >> random_join(User.c.orders) >> >> Just kidding on that last one :P >> >> Each join function requires one or three positional arguments. The >> single-arg version takes a join path as it's argument, and performs >> the join type represented by function on all joins along the path. >> A stand-alone join path is syntax sugar for join(<join path>). The >> three-arg version takes two join-able objects and a condition on >> which to join them. I realize that a two-arg version is also >> available where SA tries to find a relationship between the first >> two args, and IMHO that should be deprecated because it is error- >> prone. >> >> To do a join based on a mapper relationship, use a join path. To do >> an ad-hoc join use the three-argument version. >> >> In addition to join paths and classes, the 3-arg version should >> also accept tables, strings (entity names), select clauses, and >> other join objects as arguments. When using anything other than a >> join path as the first argument, three arguments are required. So >> this: >> >> outerjoin( >> join(User.c.orders), >> Items, >> Order.c.order_id == Item.c.order_id >> ) >> >> and would generate this: >> >> FROM users >> INNER JOIN orders >> ON users.user_id = orders.user_id >> LEFT OUTER JOIN items >> ON orders.order_id = items.order_id >> >> An optional 'alias' keyword argument may also be specified in a >> join function. If the single-arg version is used then the alias >> applies to the last table in the join path. Otherwise the alias >> applies to the second argument. >> >> join(User.c.orders, alias='ox') >> >> join( >> User, Order, >> User.c.user_id == Order.c.user_id, >> alias='ox' >> ) >> >> both of these expressions generate the following (pseudo) SQL: >> >> FROM users >> LEFT OUTER JOIN orders ox >> ON users.user_id = ox.user_id >> >> >> >> Possible alternative syntax: >> >> User.c.join(Order) >> >> The biggest problem with this alternative is that it clutters the >> User.c namespace. Like we were saying earlier: >> >>> One thing we might want to change is the 'alias' function. >>> Since the 'orders' table may have a column named 'alias', it might >>> be better to make it a stand-alone function like this: >>> alias(c.orders, 'ox') >>> >>> Mike wrote: >>>> i think this is the problem when you start munging namespaces >>>> together, as opposed to grouping them under explicit prefixes (i.e. >>>> 'c'). >> But 'c' is actually a conglomeration of namespaces including >> columns, functions, operations, etc. 'c' should only contain >> columns and relationships and everything else should be moved to a >> separate package (maybe sqlalchemy.query or even the root >> sqlalchemy package). >> >> >> I also think something similar to the SelectResults >> "transformative" style of creating joins would be useful if it was >> built-in to Query (with a few name changes such as 'join_to' should >> be 'join' and 'outerjoin_to' should be 'outer_join'). One thing I >> don't like about SelectResults is that it gives special attention >> to a few functions (avg, sum, min, max, etc.). These functions can >> incorporated into a query with the more versatile 'func.xyz' >> syntax, and would just add to the confusion about how to use >> functions. >> >> >> Anyway, there's another brain dump for now. >> >> ~ Daniel >> > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
