FastAPI+SQLModel一对多关系嵌套响应模型实现问题

你的代码存在三个核心问题导致嵌套响应的bins字段为空：

1. 响应模型字段类型与映射错误
2. SQLModel关系默认懒加载未触发预加载
3. 实体类重复定义字段引发冲突

修正后的代码 #

schemas.py #

from typing import Optional
from sqlmodel import Field, Relationship, SQLModel

class BinaryBase(SQLModel):
    product_id: int
    software_install_path: Optional[str] = None
    host_id: int = Field(foreign_key="host.id", nullable=False)

# 移除重复的host_id定义，已从BinaryBase继承
class Binary(BinaryBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    host: "Host" = Relationship(back_populates="binaries")

class HostBase(SQLModel):
    name: str
    region: Optional[str] = None
    os_version: Optional[str] = None
    network: Optional[int] = None

class Host(HostBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    binaries: list[Binary] = Relationship(back_populates='host')

# 修正响应模型：字段类型改为BinaryBase，与实体类关系字段映射
class HostReadWithBinary(HostBase):
    # 方式1：保持返回字段名为bins，通过alias映射实体类的binaries
    bins: list[BinaryBase] = Field(default=[], alias="binaries")
    
    class Config:
        populate_by_name = True  # 开启别名映射

    # 方式2：直接使用与实体类一致的字段名（更简洁）
    # binaries: list[BinaryBase] = []

class BinaryReadWithHost(BinaryBase):
    host: HostBase  # 修正类型为HostBase

main.py #

from fastapi import Depends, FastAPI
from sqlmodel import Session, col, select, selectinload  # 导入预加载工具

...

@app.get(
    "/binaries/",
    response_model=list[HostReadWithBinary]
)
def get_binary(name: Optional[str] = None, session: Session = Depends(get_session)):
    # 使用selectinload预加载Host关联的binaries数据
    query = select(Host).options(selectinload(Host.binaries)).limit(100)
    if name:
        query = query.where(col(Host.name).contains(name.lower()))

    return session.exec(query).all()

关键修正说明 #

1. 字段定义去重：Binary类重复定义host_id会导致ORM映射冲突，直接继承BinaryBase的字段即可。
2. 响应模型修正：
   • HostReadWithBinary的bins字段类型必须是list[BinaryBase]（而非HostBase），才能正确序列化Binary数据。
   • 通过alias或字段名一致的方式，将响应模型字段与实体类的binaries关系字段绑定。
3. 预加载关联数据：SQLModel的Relationship默认是懒加载（lazy="select"），序列化时不会主动加载关联数据，必须用selectinload在查询阶段预加载，确保返回的Host对象包含关联的Binary列表。

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