Is there a way to paginate related data in sqlmodel on the frontend

Question

The sqlmodel docs gives an example of two classes

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

    heroes: List["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[Team] = Relationship(back_populates="heroes")

I could get a Team object using the following code example

def get_team():
    with Session(engine) as session:
        statement = select(Team).where(Team.name == "avengers")
        result = session.exec(statement)
        avengers = result.one()
        return avengers

and doing avengers.heroes should return a list of all heroes related to that object but What if the list contains thousands of items? is there a way to paginate this without having to make a separate query to the heroes table by myself?

Answer 1

To do this, you have to work with the underlying SQLAlchemy library, which you can do using the sa_relationship_kwargs argument to Relationship.

As mentioned in this answer, if you specify the relationship as dynamic, you can then paginate it. Using SQLModel, it looks something like this:

class Team(SQLModel, table=True):
    ...
    heroes: List["Hero"] = Relationship(
        back_populates="team",
        sa_relationship_kwargs={"order_by": "Hero.name", "lazy": "dynamic"},
    )

# When calling it
first_ten_heroes = team.heroes[:10]  # or team.heroes.limit(10)