[sqlalchemy] Re: Cumulative Select
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 -
> 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 sayselect(...).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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Cumulative Select
I had to put one little "trick" in here to make the subquery -
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 sayselect(...).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
-~--~~~~--~~--~--~---
