Deep nesting response model in fastapi using sqlmodel

Question

I would like here to iterate on this previous question. But now I have a relationship among 3 tables. I believe it's useful here to look at an entity relationship diagram to better understand the structure of the underlying database.

The schemas.py relates this picture into code, where Host has a one to many relationship with Binary which in turn has a many to one relationship with 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 router function call is this one:

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

The above code works and I get a response as such:

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

However since this response has a product_id, and the Binary table is linked through the Map_Products_All table via this attribute and relationship model:

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

I would like to get the information from MapBase into the response model above. I am bit confused on how to achieve that since it involves connecting 3 tables.