How to reference a column in the select clause in the order clause in SQLAlchemy like you do in Post-CodePudding

In Postgres if one of your columns is a big complicated expression you can just say ORDER BY 3 DESC where 3 is the order of the column where the complicated expression is. Is there anywhere to do this in SQLAlchemy?

CodePudding user response：

As Gord Thompson observes in this comment, you can pass the column index as a text object to group_by or order_by:

q = sa.select(sa.func.count(), tbl.c.user_id).group_by(sa.text('2')).order_by(sa.text('2'))

serialises to

SELECT count(*) AS count_1, posts.user_id 
FROM posts GROUP BY 2 ORDER BY 2

There are other techniques that don't require re-typing the expression.

You could use the selected_columns property:

q = sa.select(tbl.c.col1, tbl.c.col2, tbl.c.col3)
q = q.order_by(q.selected_columns[2]) # order by col3

You could also order by a label (but this will affect the names of result columns):

q = sa.select(tbl.c.col1, tbl.c.col2, tbl.c.col3.label('c').order_by('c')