使用 sqlmodel 在 fastapi 中深度嵌套响应模型

我想在这里重复一下之前的内容question https://stackoverflow.com/questions/74719687/getting-joined-tables-from-sqlmodel-as-a-nested-responde-model-in-fastapi?noredirect=1#comment131876394_74719687。但现在我有3个表之间的关系。我相信在这里查看实体关系图可以更好地理解底层数据库的结构。

The schemas.py将这张图片关联到代码中，其中Host与 具有一对多关系Binary反过来又与Map_Products_All.

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


# Binary Data Model

class BinaryBase(SQLModel):
    software_install_path: Optional[str] = None


class Binary(BinaryBase,table=True):
    id: Optional[int] = Field(default=None,primary_key=True)
    host_id: int = Field(foreign_key="host.id",nullable=False)
    host: "Host" = Relationship(back_populates="binaries")
    product_id: int = Field(foreign_key="map_products_all.id",nullable=False)
    product: "Map_Products_All" = Relationship(back_populates="bins")


# HOST Data model

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



class HostReadWithBinary(HostBase):
    binaries: list[BinaryBase] = []

class BinaryReadWithHost(BinaryBase):
    host: HostBase



# Mapping Data Model

class MapBase(SQLModel):
    eim_product_id: Optional[int] = None
    vendor: Optional[str] = None
    name: Optional[str] = None
    eim_name: Optional[str] = None
    manufacturer: Optional[str] = None


class Map_Products_All(MapBase,table=True):
    id: Optional[int] = Field(default=None,primary_key=True)
    bins: list[Binary] = Relationship(back_populates="product")
    lev: Optional[int] = None

The main.py路由器函数调用是这样的：

from schemas import HostBase,Host,BinaryBase,Binary,HostReadWithBinary,BinaryReadWithHost,MapBase,Map_Products_All,EimBase,Eim,EimMap,RemediationInput,Remediation
from fastapi import FastAPI,Depends,HTTPException
from sqlmodel import SQLModel,Session,select,col

    def get_session():
        with Session(engine) as session:
            try:
                yield session
            finally:
                session.close()

app = FastAPI()

@app.on_event("startup")
def on_startup():
    SQLModel.metadata.create_all(engine)


@app.get(
    "/binaries/",
    response_model=list[HostReadWithBinary]
)
def get_binary(name: Optional[str] = None,session: Session = Depends(get_session)) -> list[Host]:  
    query = select(Host).limit(100)
    if name:
        query = query.where(col(Host.name).contains(name.lower()))
    
    return session.exec(query).all()

上面的代码有效，我得到这样的响应：

    [
      {
        "name": "hkg3pl0479",
        "region": "HK",
        "os_version": "Red Hat 6.10",
        "network": 3,
        "binaries": [
          {
            "software_install_path": "/opt/appd/machineagent",
            "product_id": 21
          }
        ]
      },
...

然而，由于这个回应有一个product_id，以及Binary表通过以下方式链接Map_Products_All通过此属性和关系模型的表：

product_id: int = Field(foreign_key="map_products_all.id",nullable=False)
product: "Map_Products_All" = Relationship(back_populates="bins")

我想从以下位置获取信息MapBase进入上面的响应模型。我对如何实现这一点有点困惑，因为它涉及连接 3 个表。