I am trying to achieve the following query (I think it is called a
cross-join):
SELECT nsm_1.id AS nsm_1_id, sum(costregister_1.costvalue /
currencycross_1.crossvalue) AS costvalue,
max(costregister_1.modifieddate) AS modifieddate
FROM nsm AS nsm_1, nsm AS nsm_2
JOIN costregister AS costregister_1 ON costregister_1.nsm_id =
nsm_2.id
JOIN currencycross AS currencycross_1 ON currencycross_1.crosscurrency
= costregister_1.costcurrency
WHERE nsm_2.lft >= nsm_1.lft AND nsm_2.lft <= nsm_1.rgt AND
currencycross_1.reference = :reference_1 GROUP BY nsm_1.id
I am nearly there but cannot work out how to perform the join between
the aliased nsm tables:
FROM nsm AS nsm_1, nsm AS nsm_2
Below is the result of the query generated with SQLAlchemy:
SELECT nsm_1.id AS nsm_1_id, sum(costregister_1.costvalue /
currencycross_1.crossvalue) AS costvalue,
max(costregister_1.modifieddate) AS modifieddate
FROM nsm AS nsm_1 JOIN nsm AS nsm_2 ON nsm_2.id = nsm_1.id JOIN
costregister AS costregister_1 ON costregister_1.nsm_id = nsm_2.id
JOIN currencycross AS currencycross_1 ON currencycross_1.crosscurrency
= costregister_1.costcurrency
WHERE nsm_2.lft >= nsm_1.lft AND nsm_2.lft <= nsm_1.rgt AND
currencycross_1.reference = :reference_1 GROUP BY nsm_1.id
and this is the code that generates the query:
tbl1 = model.aliased(model.NSM)
tbl2 = model.aliased(model.NSM)
tbl3 = model.aliased(model.CostRegister)
tbl4 = model.aliased(model.CurrencyCross)
query = self.session.query(tbl1.id,\
model.func.sum(tbl3.costvalue/
tbl4.crossvalue).label('costvalue'),\
model.func.max(tbl3.modifieddate).label('modifieddate'))\
.join((tbl2, tbl2.id==tbl1.id))\
.join((tbl3, tbl3.nsm_id==tbl2.id))\
.join((tbl4, tbl4.crosscurrency==tbl3.costcurrency))\
.filter(tbl2.left>=tbl1.left)\
.filter(tbl2.left<=tbl1.right)\
.filter(tbl4.reference==145)\
.group_by(tbl1.id)
I have tried several permutations on the join but it is beyond my
current knowledge how to achieve what I need.
Any assistance would be greatly appreciated.
--
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.
