And again you made my day...
On Jul 24, 7:17 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> I had to put one little "trick" in here to make the subquery -
> <something> work, which is something i should look into; otherwise it
> went straight in.
>
> from sqlalchemy import *
>
> transaction = table('transaction',
> column('id'),
> column('id_product'),
> column('price'),
> column('quantity')
> )
>
> f1 = transaction.alias('f1')
> f2 = transaction.alias('f2')
>
> subquery = select([func.coalesce(func.sum(f2.c.quantity))], and_
> (f2.c.id>f1.c.id, f2.c.id_product==bindparam('f2item')), scalar=True)
>
> s = select([f1.c.id, subquery.label('foo') - bindparam('offset').label
> ('offset')], and_(
> f1.c.id_product==bindparam('f1item'),
> bindparam('something') <= subquery
> ) , order_by=[desc(f1.c.id)], limit = 1
> )
>
> print s
>
> I think in 0.4 im going to deprecate "scalar=True" and instead have
> you say....select(...).scalar()
>
> On Jul 24, 2007, at 9:38 AM, Koen Bok wrote:
>
>
>
> > I need to do a cumulative select on postgres, and I got it to work
> > with some extreme SQL query, but I was wondering if it could be done
> > more easily without having to drop to SQL but with SQLAlchemy
> > statements.
>
> > Let's say I have a table with stock transactions like this:
>
> > Transaction
> > id
> > id_product
> > price
> > quantity
>
> > And it is filled like this:
>
> > 1 1 12 10
> > 2 1 13 5
> > 3 1 12 3
> > 4 1 11 6
> > 5 1 10 5
>
> > Now at moment X my stock is 13 and I want to know the costs for each
> > product in my stock. So I add a cumulative column to select on and
> > expect to get the last three rows back as their cumulative total is <=
> > as my stock:
>
> > CUM
> > 1 1 12 10 29
> > 2 1 13 5 19
> > 3 1 12 3 14 this
> > 4 1 11 6 11 this
> > 5 1 10 5 5 and this...
>
> > Extra info:
>
> > This is the query I currently use to get the transaction ID and offset
> > back:
>
> > SELECT
> > f1.id,
> > (
> > SELECT
> > coalesce(sum(quantity), 0)
> > FROM transaction f2
> > WHERE f2.id>=f1.id
> > AND f2.id_item = %s
> > ) - %s as offset
> > FROM
> > transaction f1
> > AND f1.id_item = %s
> > AND %s <= (
> > SELECT
> > coalesce(sum(quantity), 0)
> > FROM transaction f2
> > WHERE f2.id>=f1.id
> > AND f2.id_item = %s
> > )
> > ORDER BY f1.id DESC LIMIT 1
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
