Control Output Strucure AKA Flatten nested output

[javivdm]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional, List

from sqlmodel import SQLModel, Relationship, create_engine, Session, Field, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    team_id: Optional[int] = Field(foreign_key="team.id")

    team: Optional["Team"] = Relationship(back_populates="heroes")


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    city_id: Optional[int] = Field(foreign_key="city.id")

    city: Optional["City"] = Relationship(back_populates="teams")
    heroes: List[Hero] = Relationship(back_populates="team")


class City(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    post_code: int

    teams: List[Team] = Relationship(back_populates="city")


class CityRead(SQLModel):
    id: int
    name: str
    post_code: int


class TeamRead(SQLModel):
    id: int
    name: str
    city: CityRead


class HeroRead(SQLModel):
    id: int
    name: str

    city: CityRead


engine = create_engine("sqlite:///.deleted/database.db")

SQLModel.metadata.create_all(engine)

city_1 = City(name="New York", post_code="2022")
city_2 = City(name="Chicago", post_code="3033")
team_1 = Team(name="Revengers", city=city_1)
team_2 = Team(name="Y Women", city=city_2)
hero_1 = Hero(name="Grey Hound", team=team_1)
hero_2 = Hero(name="Guess Girl", team=team_2)

with Session(engine) as session:
    session.add(hero_1)
    session.add(hero_2)
    session.commit()
    session.refresh(hero_1)
    session.refresh(hero_2)

    results = session.exec(select(Hero, City).join(Hero.team).join(Team.city)).all()
    print(results)

    hero_read_list: List[HeroRead] = [HeroRead.from_orm(hero) for hero in results]
    print(hero_read_list)

Description

The problem is that I want to be able to declare the creation of an output model in a declarative way, at the moment of declaring the model. I want to be able with the above code to have a HeroRead class that has the city info without the team object. So that the output in a JSON response would be:

{
  "id": id,
  "name": name,
  "city": {
    "id": id,
    "name": name,
    "post_code": postcode,
    }
}

instead of:

{
  "id": id,
  "name": name,
  "team": {
    "id": id,
    "name": name,
    "city": {
      "id": id,
      "name": name,
      "post_code": postcode,
      }
    }
}

I could do that manually going over every hero object in the results and building it as a dict, but when using FastAPI and an endpoint with a lot of data, that will use pagination as well I cannot afford to do that. Therefore I expect some way of passing the city object from the query to the output model, kind of like in DRF you have the source attribute in a serializer field.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

Python 3.9.10

Additional Context

No response

[byrman]

You could write your own GetterDict:

from pydantic.utils import GetterDict


class HeroGetter(GetterDict):

    def get(self, key: str, default: Any) -> Any:
        if key == "city":
            return self._obj.team.city
        return super().get(key, default)


class HeroRead(SQLModel):
    id: int
    name: str
    
    city: CityRead

    class Config:
        getter_dict = HeroGetter

[javivdm]

Amazing, thank you so much :)