Can I sort nested connections as part of a query using ORM rather than a separate for loop?

[gpspake]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

I have a basic one to many relationship. Releases have Tracks via a release_id property on Track. Track has a sort_order property that's an int used to sort the tracks. tracks should always be sorted by sort_by.

The models look something like this:

class Track(TrackBase, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    # ...
    sort_order: int

    release_id: uuid.UUID | None = Field(default=None, foreign_key="release.id")
    release: Release | None = Relationship(back_populates="tracks")

class Release(ReleaseBase, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    # ...
    tracks: list["Track"] = Relationship(back_populates="release")

And my endpoint looks like this:

@router.get("/", response_model=ReleasesOut)
def read_releases(session: SessionDep, skip: int = 0, limit: int = 100) -> Any:
    """
    Retrieve releases.
    """

    count_statement = select(func.count()).select_from(Release)
    count = session.exec(count_statement).one()
    statement = select(Release).offset(skip).limit(limit)
    releases = session.exec(statement).all()

    for release in releases:
        if release.tracks:
            release.tracks.sort(key=lambda track: track.sort_order)

    releases_public = list(map(release_public_to_release_out, releases))

    return ReleasesOut(data=releases_public, count=count)

In this example I'm sorting the tracks after the query is executed. This works but there are some issues:

• This seems like a non-optimal solution and I would think it would be preferable to do the sort as part of the query somehow.
• The Track relationship is not the only nested relationship I need to sort like this; there are also labels and artists that need to be returned in order and I'd like to avoid needing a for loop for each one.

### Description

Sorting nested relationships when querying model.

### Operating System

macOS

### Operating System Details

_No response_

### SQLModel Version

0.0.20

### Python Version

3.10.14

### Additional Context

_No response_

[YuriiMotov]

You can specify the sort order for relationship field:

heroes: List["Hero"] = Relationship(back_populates="team", sa_relationship_kwargs={"order_by": "Hero.name"})

Runnable code example in the details:

from typing import Optional, List
from sqlmodel import SQLModel, Field, Relationship, create_engine, Session, select


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="team", sa_relationship_kwargs={"order_by": "Hero.name"})


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    team_id: Optional[int] = Field(default=None, foreign_key="team.id")

    team: Optional[Team] = Relationship(back_populates="heroes")


# --- Setup database ---
engine = create_engine("sqlite:///", echo=False)
SQLModel.metadata.create_all(engine)

# --- Example usage ---
with Session(engine) as session:
    # Create a team
    avengers = Team(name="Avengers", headquarters="New York")
    session.add(avengers)
    session.commit()
    session.refresh(avengers)

    # Create heroes and link them to the team
    ironman = Hero(name="Iron Man", secret_name="Tony Stark", age=45, team=avengers)
    hulk = Hero(name="Hulk", secret_name="Bruce Banner", age=40, team=avengers)
    superman = Hero(name="Superman", secret_name="???", age=30, team=avengers)

    session.add_all([ironman, hulk, superman])
    session.commit()

    # Query with relationship
    statement = select(Team).where(Team.name == "Avengers")
    result = session.exec(statement).one()
    print("Heroes:", [hero.name for hero in result.heroes])

Output:

Heroes: ['Hulk', 'Iron Man', 'Superman']