column label and order by

[rajasekhar911]

Hi guys,

how do i apply order by on a column with a label.
My requirement is like this

class x
id,
amount,
date

i have to group based on id and take sum of amount within a date
range.

i am applying a label to sum of amount
now how do i order based on that so that i can get top 5 ..

session.query( func.sum(x.amount).label('tot_amount'), x.id ).
filter(x.date>fromdate).filter(x.date<todate).
.group_by(x.id)
.order_by(?????)
.limit(5)


thanks.

[rajasekhar911]

anyone??

[King Simon-NFHD78]

How about (untested):

tot_amount = func.sum(x.amount).label('tot_amount')
session.query(tot_amount, x.id).
filter(x.date>fromdate).filter(x.date<todate).
.group_by(x.id)
.order_by(tot_amount)
.limit(5)

Simon

[Mike Conley]

And you do need to quote the column name in order_by also.

session.query(func.sum(X.amount).label('tot_amount')).group_by(X.date).order_by('tot_amount').limit(10)

generates code

SELECT sum(x.amount) AS tot_amount
FROM x GROUP BY x.date ORDER BY tot_amount
 LIMIT 10 OFFSET 0

[rajasekhar911]

session.query( func.sum(x.amount).label('tot_amount'), x.id ).
filter(x.date>fromdate).filter(x.date<todate).
.group_by(x.id)

.order_by('tot_amount DESC')
.limit(5)