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
-~----------~----~----~----~------~----~------~--~---
