Hi Mike,
Happy Thanksgiving :-)
On 22/11/2013 15:55, Michael Bayer wrote:
I don't *need* to ;-) ...but I was wondering how, starting with a raw string
and a couple of variables to bind in, I could get a cte that I could join
against an ORM-mapped object?
uh well we don’t exactly have that, there’s a proposal to enhance text() such
that you could give it a .c. collection with another method though I’m not
finding that right now, which is a little odd, maybe I was showing someone on
the ML how to do it. it’s just another SQL construct.
Okay, here's somewhere that it'd be really handy. So, I have a fairly
complex query, that basically returns the primary keys of two ORM-mapped
classes and some aggregate data:
select
p.idea_id,
p.instrument_id,
sum(p.quantity),
sum(c.quantity) as cost,
least(min(p.timestamp), min(c.timestamp)) as opened
from transaction p join transaction c on
p.idea_id = c.idea_id and
p.instrument_id!='gbp' and
c.instrument_id='gbp'
group by p.idea_id, p.instrument_id
having sum(p.quantity)>0 order by idea_id;
So far, I got:
inst = aliased(Part)
unit = aliased(Part)
query = session.query(
inst.idea_id,
inst.instrument_id,
func.sum(inst.quantity),
func.sum(unit.quantity).alias('cost'),
func.least(func.min(inst.timestamp),
func.min(unit.timestamp)).alias('opened'),
).select_from(
inst
).join(
unit,
and_(inst.idea_id==unit.idea_id,
inst.instrument_id != 'gbp',
unit.instrument_id == 'gbp')
).group_by(
inst.idea_id,
inst.instrument_id,
)
...which works if I remove the aliases, but with them, I get:
ProgrammingError: (ProgrammingError) missing FROM-clause entry for table
"transaction_2"
LINE 2: FROM sum(transaction_2.quantity) AS cost, least(min(transact...
^
'SELECT transaction_1.idea_id AS transaction_1_idea_id,
transaction_1.instrument_id AS transaction_1_instrument_id,
sum(transaction_1.quantity) AS sum_1, cost.sum_2 AS cost_sum_2,
opened.least_1 AS opened_least_1 \nFROM sum(transaction_2.quantity) AS
cost, least(min(transaction_1.timestamp), min(transaction_2.timestamp))
AS opened, transaction AS transaction_1 JOIN transaction AS
transaction_2 ON transaction_1.idea_id = transaction_2.idea_id AND
transaction_1.instrument_id != %(instrument_id_1)s AND
transaction_2.instrument_id = %(instrument_id_2)s GROUP BY
transaction_1.idea_id, transaction_1.instrument_id' {'instrument_id_2':
'gbp', 'instrument_id_1': 'gbp'}
...which is a little confusing.
What am I doing wrong?
Would be easier if I could just take my text query, plugin in the params
and return a cte...
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
