how use alias in sqlachemy

Question

I have a sql query as follows

select cloumn1,column2,count(column1) as c 
from Table1 where user_id='xxxxx' and timestamp > xxxxxx
group by cloumn1,column2
order by  c desc limit 1;

And I successed in write the sqlalchemy equvalent

result = session.query(Table1.field1,Table1.field2,func.count(Table1.field1)).filter(
    Table1.user_id == self.user_id).filter(Table1.timestamp > self.from_ts).group_by(
    Table1.field1,Travelog.field2).order_by(desc(func.count(Table1.field1))).first()

But I want to avoid using func.count(Table1.field1) in the order_by clause.

How can I use alias in sqlalchemy? Can any one show any example?

Answer 1

Aliases are for tables; columns in a query are given a label instead. This trips me up from time to time too.

You can go about this two ways. It is sufficient to store the func.count() result is a local variable first and reuse that:

field1_count = func.count(Table1.field1)

result = session.query(Table1.field1, Table1.field2, field1_count).filter(
    Table1.user_id == self.user_id).filter(Table1.timestamp > self.from_ts).group_by(
    Table1.field1, Travelog.field2).order_by(desc(field1_count)).first()

The SQL produced would still be the same as your own code would generate, but at least you don't have to type out the func.count() call twice.

To give this column an explicit label, call the .label() method on it:

field1_count = func.count(Table1.field1).label('c')

and you can then use that same label string in the order_by clause:

result = session.query(Table1.field1, Table1.field2, field1_count).filter(
    Table1.user_id == self.user_id).filter(Table1.timestamp > self.from_ts).group_by(
    Table1.field1, Travelog.field2).order_by(desc('c')).first()

or you could use the field1_count.name attribute:

result = session.query(Table1.field1, Table1.field2, field1_count).filter(
    Table1.user_id == self.user_id).filter(Table1.timestamp > self.from_ts).group_by(
    Table1.field1, Travelog.field2).order_by(desc(field1_count.name)).first()