FastAPI多对多关联含额外关系的Pydantic模型定义问询

首先得调整SQLAlchemy的模型关系——原来用secondary参数的多对多关系没法直接获取中间表的额外字段，得换成通过中间表的一对多关联，这样才能拿到中间表里的protocol_status数据：

修改后的models.py代码：

class Devices(Base):
    __tablename__ = "devices"
    id = Column(Integer, primary_key=True, unique=True, index=True)
    name = Column(String(255))
    description = Column(String(255), nullable=True)
    status_id = Column(Integer, ForeignKey('status.id'))
    status = relationship("Status", backref="devices")
    # 替换原多对多关系，改为关联中间表
    device_protocols = relationship("DeviceProtocols", back_populates="device")

class Status(Base):
    __tablename__ = "status"
    id = Column(Integer, primary_key=True, unique=True, index=True)
    name = Column(String(255))
    description = Column(String(255), nullable=True)

class Protocols(Base):
    __tablename__ = "protocols"
    id = Column(Integer, primary_key=True, unique=True, index=True)
    name = Column(String(255))
    # 替换原多对多关系，改为关联中间表
    device_protocols = relationship("DeviceProtocols", back_populates="protocol")

class DeviceProtocols(Base):
    __tablename__ = "device_protocols"
    device_id = Column(Integer, ForeignKey('devices.id'), primary_key=True)
    protocol_id = Column(Integer, ForeignKey('protocols.id'), primary_key=True)
    protocol_status_id = Column(Integer, ForeignKey('status.id'), nullable=True)
    protocol_status = relationship("Status")
    # 添加与两端模型的双向关联
    device = relationship("Devices", back_populates="device_protocols")
    protocol = relationship("Protocols", back_populates="device_protocols")

接下来修改Pydantic Schema，新增一个对应中间表的Schema来承载protocol和protocol_status数据，再调整DeviceRead的字段类型：

修改后的schemas.py代码：

from pydantic import BaseModel

class StatusBase(BaseModel):
    name: str
    description: str | None = None

    class Config:
        orm_mode = True

class StatusReadSimple(StatusBase):
    id: int  # 注意：数据库里是Integer类型，别用str，保持类型一致

class ProtocolBase(BaseModel):
    name: str
    class Config:
        orm_mode = True

class ProtocolSimple(ProtocolBase):
    id: int  # 同样统一为int类型

# 新增中间表对应的Schema，用来包装协议和状态
class DeviceProtocolWithStatus(BaseModel):
    protocol: ProtocolSimple
    protocol_status: StatusReadSimple | None = None  # 对应数据库nullable=True，允许为空

    class Config:
        orm_mode = True

class DeviceBase(BaseModel):
    name: str
    class Config:
        orm_mode = True

class DeviceRead(DeviceBase):
    id: int
    description: str | None = None
    status: StatusReadSimple | None = None
    # 把原来的protocols字段换成中间表Schema的列表
    protocols: list[DeviceProtocolWithStatus]

最后查询数据的时候，记得预加载关联数据，避免N+1查询问题，示例代码：

from sqlalchemy.orm import joinedload

# 查询单个设备
device = db.query(Devices)\
    .options(
        joinedload(Devices.status),
        # 预加载中间表，以及关联的协议和状态
        joinedload(Devices.device_protocols).joinedload(DeviceProtocols.protocol),
        joinedload(Devices.device_protocols).joinedload(DeviceProtocols.protocol_status)
    )\
    .filter(Devices.id == 3)\
    .first()

# 转成Pydantic模型返回
return DeviceRead.from_orm(device)

这样处理后，返回的JSON结构就会和你期望的一致了。

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