In sqlalchemy, is there a way to sort so that empt

问题:

I have a pretty standard setup and want to sort by a column:

someselect.order_by(asc(table1.mycol))

However, I want rows with '' or NULL for mycol to appear at the end of the results. Is there a way to do this?

回答1:

SQLAlchemy has a NULLS LAST modifier for ORDER BY expressions:

sqlalchemy.sql.expression.nullslast(column)

See http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.nullslast

回答2:

On databases that support NULLS LAST, you can sort NULLs at the end by doing

SELECT * FROM table1 ORDER BY mycol ASC NULLS LAST;

You need to convert '' to NULLs so you can do this (which I recommend doing anyway), either in the data or as part of the query:

SELECT * FROM table1 ORDER BY (CASE mycol WHEN '' THEN NULL ELSE mycol END) ASC NULLS LAST;

Alternatively, a more portable approach is

SELECT * FROM table1 ORDER BY (CASE WHEN mycol IS NULL OR mycol = '' THEN 1 ELSE 0 END) ASC, mycol;

To write this in SQLAlchemy:

.order_by(case([(or_(tabel1.mycol.is_(None), table1.mycol == ""), 1)],
               else_=0), table1.mycol)