On Nov 28, 2013, at 7:54 PM, Chris Withers <ch...@simplistix.co.uk> wrote:
> 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’),
bzzt - when you say func.foo(x).alias(), you’re interpreting the Function as a
“SELECT * FROM func AS foo” type of thing. Use func.sum(..).label(‘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 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
