Bi-directional one to many relationship

[petercinibulk]

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 List, Optional

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


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

    heroes: List["Hero"] = Relationship(back_populates="team")


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

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[Team] = Relationship(back_populates="heroes")

Description

When I create a one to many relationship such as the example code which I got from the docs, the team_id is auto populated with the id of the team when I add a team to a hero.

I want the reverse to happen. I want to be able to have a field on the Team class that contains a List of the hero ids. I know there is a way to get all of the heroes as mentioned here (https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/), but for this scenario, I only want a List of the ids of the Heros.

I know the below code doesn't work but it would be nice to do something like it and have the ids automatically be there.

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    headquarters: str
    hero_ids: Optional[List[int]] = Field(default=None, foreign_key="hero.id")  // New
    heroes: List["Hero"] = Relationship(back_populates="team")

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

Python 3.8.6

Additional Context

No response

[JarroVGIT]

Does it really has to be a Field? Do you want it to be an active relationship as well, like if you would add an hero.id to the list Team.hero_ids it would automatically add that Hero to the Teams.heroes list as well?

If not, and you are only looking for a way to easily extract a list of Hero id's associated with a Team instance, you could just create a custom getter. Since a list of Hero objects is already coming with a Team instance, you can do something like this in the Team class:

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

    heroes: List["Hero"] = Relationship(back_populates="team")
    def hero_ids(self):
        return [hero.id for hero in self.heroes]

I tested this approach with a test project I had open, using FastAPI with SQLmodel and here is a full example of it. Take not of the last path. (Note I used postgres but if you want you can just comment that line out and uncomment the 4 lines below with the SQLite engine):

from typing import List, Optional
from fastapi.exceptions import HTTPException
from sqlmodel import SQLModel, Field, create_engine, Session, Relationship
from fastapi import FastAPI, Depends
import uvicorn

class TeamBase(SQLModel):
    team_name: str


class Team(TeamBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    
    heroes: List["Hero"] = Relationship(back_populates="team")

    def hero_ids(self):
        return [hero.id for hero in self.heroes]
  
class TeamRead(TeamBase):
    id: int


class HeroBase(SQLModel):
    hero_name: str
    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    
    team: Optional[Team] = Relationship(back_populates="heroes")


class HeroRead(HeroBase):
    id: int 


class TeamReadWithHeroes(TeamRead):
    heroes: List["HeroRead"] = []


class HeroReadWithTeam(HeroRead):
    team: Optional[TeamRead] = None


engine = create_engine("postgresql://postgres:mysecretpassword@localhost:5432/testdb", echo=True)
# sqlite_file_name = "db.db"
# sqlite_url = f"sqlite:///{sqlite_file_name}"
# connect_args = {"check_same_thread": False}
# engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)

t1 = Team(team_name="t1")
h1 = Hero(hero_name="h1", team=t1)
h2 = Hero(hero_name="h2", team=t1)

def get_session():
    with Session(engine) as session:
        yield session
 
app = FastAPI()

@app.on_event("startup")
def on_startup():
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        session.add(t1)
        session.commit()
        session.refresh(t1)
 

@app.get("/team/{team_id}", response_model=TeamReadWithHeroes)
def home(*, team_id: int, session: Session = Depends(get_session)):
    team = session.get(Team, team_id)
    print(team)
    return team 

@app.get("/heroes/{hero_id}", response_model=HeroReadWithTeam)
def read_hero(*, hero_id: int, session: Session = Depends(get_session)):
    hero = session.get(Hero, hero_id)
    if not hero:
        raise HTTPException(status_code=404, detail="Hero not found")
    return hero

@app.get("/teamheroids/{team_id}")
def read_team_heroids(*, team_id: int, session: Session = Depends(get_session)):
    team = session.get(Team, team_id)
    print(team.hero_ids())
    return(team.hero_ids())

if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)

[antont]

Also SA column properties and normal python properties can be used for such, we do that to have it working with generic crud code. But with a custom getter that way is simple and nice, we have similar things too.

[tiangolo]

Thanks for the help @JarroVGIT ! 🍰

And thanks for coming back to close the issue @petercinibulk ☕