Michael Bayer wrote:
> but what happens if i say:
>
> q.select(or_(User.c.orders.items.item_name == 'item#4',
> User.c.orders.items.item_name == 'item #5'))
>
> if we naively convert c.orders.items.item_name=='item #4' into
> "user.user_id=orders.user_id and orders.order_id=items.order_id and
> items.item_name='item #4", then the other clause for item #5
> generates into the same thing and you get an inefficient query. i
> wonder also if some expressions above dont work correctly if the join
> conditions are repeated like that.
>
> its still better to say:
>
> q.select(or_(Item.c.item_name == 'item#4', Item.c.item_name == 'item
> #5'), from_obj=[c.orders.items])
>
> isnt it ? (User.c.orders.items would be a synonym for query.join_via
> ('orders', 'items'))
>
Ok, here are some more thoughts about how this might work.
This query:
q.select(or_(
c.orders.items.item_name == 'item#4',
c.orders.items.item_name == 'item #5'
))
Should generate something similar to this SQL:
SELECT ...
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id
INNER JOIN items i
ON o.order_id = i.order_id
WHERE i.item_name = 'item#4'
OR i.item_name = 'item #5'
For the given clause, each occurrence of User.c.orders.items is combined into a
single join path from users to items, and every occurrence of 'item_name' uses
the same table alias 'i'. Is that too simplistic? Here are a few more examples:
q.select(and_(
c.orders.order_total > 50,
or_(
c.orders.items.item_name == 'item #4',
c.orders.items.item_name == 'item #5',
)
))
SELECT ...
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id
INNER JOIN items i
ON o.order_id = i.order_id
WHERE o.order_total > 50 AND
(
i.item_name = 'item#4' OR
i.item_name = 'item #5'
)
# -------------------
q.select(or_(
c.orders.items.item_name == 'item #4',
and_(
c.orders.order_total > 50,
c.orders.items.item_name == 'item #5',
)
))
SELECT ...
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id
INNER JOIN items i
ON o.order_id = i.order_id
WHERE i.item_name = 'item#4' OR
(
o.order_total > 50 AND
i.item_name = 'item #5'
)
Notice how the join path for Users.c.orders is superimposed on the join path
for Users.c.orders.items. It should be possible to use the alias function to
force separate joins like this:
q.select(and_(
c.orders.alias('ox').order_total > 50,
or_(
c.orders.alias('o').items.item_name == 'item #4',
c.orders.alias('o').items.item_name == 'item #5',
)
))
SELECT ...
FROM users u
INNER JOIN orders ox
ON u.user_id = ox.user_id
INNER JOIN orders o
ON u.user_id = o.user_id
INNER JOIN items i
ON o.order_id = i.order_id
WHERE ox.order_total > 50 AND
(
i.item_name = 'item#4' OR
i.item_name = 'item #5'
)
ASIDE: 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')
~ Daniel
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
