如何在SQLModel中对关联数据实现前端分页（无需单独查询）？

问题背景 #

现有SQLModel定义的Team和Hero模型如下：

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

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")

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

通过以下代码获取Team对象后，调用avengers.heroes会返回该团队关联的所有英雄，但如果英雄数量上千，直接返回全量数据会导致性能问题，能不能不用单独查询Hero表，直接对关联数据做前端分页？

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

解决方案 #

不能直接通过avengers.heroes实现高效分页——因为这个属性默认是懒加载，调用时会一次性拉取所有关联的Hero数据，大数据量下会占用大量内存和带宽。正确的做法是在后端基于Team的条件构建Hero的分页查询，只返回当前页的数据给前端。

方式1：先查Team再分页查Hero

先获取目标Team的ID，再以此为条件查询Hero并做分页，同时统计总条数用于前端分页导航：

def get_team_heroes_paginated(team_name: str, page: int = 1, page_size: int = 20):
    with Session(engine) as session:
        # 获取目标团队
        team = session.exec(select(Team).where(Team.name == team_name)).one()
        # 分页查询该团队的英雄
        offset = (page - 1) * page_size
        heroes_statement = select(Hero).where(Hero.team_id == team.id).offset(offset).limit(page_size)
        heroes = session.exec(heroes_statement).all()
        # 统计该团队的英雄总数
        total_statement = select(func.count(Hero.id)).where(Hero.team_id == team.id)
        total = session.exec(total_statement).one()
        return {
            "heroes": heroes,
            "total": total,
            "current_page": page,
            "page_size": page_size
        }

方式2：关联查询直接分页

通过join关联Team和Hero表，直接用Team名称作为过滤条件实现分页，减少一次数据库查询：

def get_team_heroes_paginated(team_name: str, page: int = 1, page_size: int = 20):
    with Session(engine) as session:
        offset = (page - 1) * page_size
        # 关联查询分页数据
        heroes_statement = (
            select(Hero)
            .join(Team, Hero.team_id == Team.id)
            .where(Team.name == team_name)
            .offset(offset)
            .limit(page_size)
        )
        heroes = session.exec(heroes_statement).all()
        # 统计总数
        total_statement = (
            select(func.count(Hero.id))
            .join(Team, Hero.team_id == Team.id)
            .where(Team.name == team_name)
        )
        total = session.exec(total_statement).one()
        return {
            "heroes": heroes,
            "total": total,
            "current_page": page,
            "page_size": page_size
        }

关键说明 #

• 无论哪种方式，核心都是在数据库层面完成分页，只返回当前页需要的数据，避免全量加载
• avengers.heroes仅适合数据量小的场景，大数据量下必须用分页查询替代
• 返回的total字段可以让前端计算总页数，实现完整的分页导航功能

内容的提问来源于stack exchange，提问作者mtkguy