order by query

[joaopfg]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import SQLModel, Field, Session, create_engine

class Person(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    age: int

person_1 = Person(age = 46)
person_2 = Person(age = 13)
person_3 = Person(age = 27)

engine = create_engine("sqlite:///database.db")

SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(person_1)
    session.add(person_2)
    session.add(person_3)
    session.commit()

with Session(engine) as session:
    people = session.exec(select(Person)).all()
    poeple.sort(key = lambda person: person.age)

Description

In Django we can use order_by directly in SQL statement . For example, something like that:

Table.objects.filter(criterion1 & criterion2).order_by('id')

Is there some way to do something similar in SQLModel ?
For now my work around is to get a list of elements with a select query and then order it using python builtin sort function.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.5

Additional Context

No response

[yasamoka]

order_by is available from SQLAlchemy itself.

https://docs.sqlalchemy.org/en/14/tutorial/data_select.html#order-by-group-by-having

select(Person).order_by(Person.age) works for instance.

[nandyalu]

I think this needs to be added to documentation. Maybe somewhere in Tutorial

[jbethune]

The above code is correct. But some type checkers (e.g. ruff) may complain about the column not being a proper column type. You can inform the type checker that everything is fine by using the col function:

from sqlmodel import col

select(MyTable).order_by(col(MyTable.my_column))