Sqlalchemy ORDER BY DESCENDING?

Order by descending — the alternatives

Using model attributes

query = session.query(MyTable).order_by(MyTable.my_column.desc())  # Short and sweet


Using relationship definitions

class Parent(Base):
    __tablename__ = 'parent'
    children = relationship("Child", order_by="Child.some_column.desc()")  # The kids are alright


Using text expressions

from sqlalchemy import text
query = session.query(MyTable).order_by(text("my_column DESC"))  # Going old school with raw SQL


Handling NULLs explicitly

from sqlalchemy import nullsfirst, nullslast
query_nulls_first = session.query(MyTable).order_by(nullsfirst(MyTable.my_column.desc()))  # Let's rock, NULLs!
query_nulls_last = session.query(MyTable).order_by(nullslast(MyTable.my_column.desc()))  # Bye bye, NULLs!


Avoiding rookie mistakes

Checking column references

# Oopsie, this won't work. AttributeError incoming!
query = session.query(MyTable).order_by(desc("my_column"))

# Ah, the sweet smell of correct attribute references
query = session.query(MyTable).order_by(desc(MyTable.my_column))


Closing your sessions

# Always remember to close after use, just like your fridge
session.close()


Using filters before ordering

# It's raining datasets, let's filter before we get wet
query = session.query(MyTable).filter(MyTable.some_field == 'value').order_by(MyTable.my_column.desc())


Debugging unexpected results

# Hmm, the sorting isn't right. Time to don the detective hat!
print(query.statement)  # The Sherlock Holmes of SQL debugging
