I have such queries:
OI = aliased(OrderedItem, name="OrderedItem_AD")
CH = aliased(TChart, name="Chart_AD")
ORD = aliased(Order, name="Order_AD")
alreadyDone = session.query(CH.id).\
join((OI, CH.orderedItems)).\
join((ORD, OI.order)).\
join((User, ORD.user)).\
join((OrderStatus, ORD.status)).\
filter(User.name ==u"TestUser").\
filter(OrderStatus.id == 1).\
subquery()
OI2 = aliased(OrderedItem, name="OrderedItem_CUR")
CH2 = aliased(TChart, name="Chart_CUR")
ORD2 = aliased(Order, name="Order_CUR")
orderedItemsToExclude = session.query(OI2).select_from(join(OI2, CH2)).
\
join((ORD2, OI2.order)).\
join((alreadyDone, alreadyDone.c.id == CH2.id)).\
filter(ORD2.id == order.id)
Alchemy generate such queries:
alreadyDone:
SELECT "Chart_1".id
FROM "Chart" AS "Chart_1" JOIN "OrderedItem" AS "OrderedItem_1" ON
"Chart_1".id = "OrderedItem_1".chart_id JOIN "Order" AS "Order_1" ON
"Order_1".id = "OrderedItem_1"."Order_id" JOIN "User" ON "User".id =
"Order_1".user_id JOIN "OrderStatus" ON "OrderStatus".id =
"Order".status_id
WHERE "User".name = :name_1 AND "OrderStatus".id = :id_1
orderedItemsToExclude:
SELECT "OrderedItem_1"."downloadURL" AS "OrderedItem_1_downloadURL",
"OrderedItem_1".chart_id AS "OrderedItem_1_chart_id",
"OrderedItem_1"."Order_id" AS "OrderedItem_1_Order_id"
FROM "OrderedItem" AS "OrderedItem_1" JOIN "Chart" AS "Chart_1" ON
"Chart_1".id = "OrderedItem_1".chart_id JOIN "Order" AS "Order_1" ON
"Order_1".id = "OrderedItem"."Order_id" JOIN (SELECT "Chart_2".id AS
id
FROM "Chart" AS "Chart_2" JOIN "OrderedItem" AS "OrderedItem_2" ON
"Chart_2".id = "OrderedItem_2".chart_id JOIN "Order" AS "Order_2" ON
"Order_2".id = "OrderedItem_2"."Order_id" JOIN "User" ON "User".id =
"Order_2".user_id JOIN "OrderStatus" ON "OrderStatus".id =
"Order".status_id
WHERE "User".name = :name_1 AND "OrderStatus".id = :id_1) AS anon_1 ON
anon_1.id = "Chart_1".id
WHERE "Order_1".id = :id_2
The problem is that it doesn't use aliased name in join condition:
"Order_1".id = "OrderedItem"."Order_id"
must be
"Order_1".id = "OrderedItem_1"."Order_id"
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
