How to order by case, descending?

Question

I want to build this query using SQLAlchemy:

select * from t order by
case
    when t.x!=0 then t.y
    when t.x==0 then 0
end desc;

I tried the following:

db.session.query(t).order_by(
    db.func.desc(
        db.func.case([
            (t.x!=0, t.y),
            (t.x==0, 0)
        ]
    )
)

But it raised a ProgrammingError 'You have an error in your SQL syntax'. How can I write this case statement in SQLAlchemy?

Answer 1

case is not a function, and is present on the db instance. You can specify an else clause rather than a second when. You can just call .desc() on an expression rather than wrapping it with desc(). The query should look like:

db.session.query(t).order_by(db.case(((t.x != 0, t.y),), else_=0).desc())