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