On 19/11/2013 14:49, Michael Bayer wrote:
This query (which seems simple enough to me):
session.query(Part.instrument, func.sum(Part.quantity))\
.filter((Part.account_id=='td') & (Part.timestamp < date(2013, 11, 1)))\
.group_by(Part.instrument)\
.all()
we don’t support query(Cls.relationshipname) as of yet, it’s spitting out the
join condition into the columns clause, and in any case you’d need to specify a
real join() in order to get Part and Instrument into the same SELECT:
query(Instrument,
func.sum(Part.quantity)).select_from(Part).join(Part.instrument)…
Gotcha, select_from was the bit missing from my brain, so the query becomes:
session.query(Instrument, func.sum(Part.quantity))\
.select_from(Part)\
.join(Part.instrument)\
.filter((Part.account_id=='td') & (Part.timestamp < date(2013,
11, 1)))\
.group_by(Instrument)\
.all()
Okay, now I want to turn it into a CTE, outer join that to a pricing
table and select instrument, sum(quantity) and the Price object (which
might be None) from the join.
I'd guess something like:
positions = session.query(Instrument, func.sum(Part.quantity))\
.select_from(Part)\
.join(Part.instrument)\
.filter((Part.account_id=='td') & (Part.timestamp < date(2013,
11, 1)))\
.group_by(Instrument)\
.cte('positions')
session.query(Instrument, func.sum(Part.quantity), Observation)\
.select_from(positions)\
.join(Observation,
positions.instrument_id==Observation.instrument_id)\
.filter(Observation.period.contains(datetime(2013, 11, 1)))\
.all()
..but that gives:
AttributeError: 'CTE' object has no attribute 'instrument_id'
What syntax am I looking for?
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
