[M2M] Query dependent incl. `link_model` fields

[Pk13055]

First Check

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

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

from typing import List, Optional

from sqlalchemy.orm import joinedload
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select

class Membership(SQLModel, table=True):
    team_id: Optional[int] = Field(
        default=None, foreign_key="team.id", primary_key=True
    )
    hero_id: Optional[int] = Field(
        default=None, foreign_key="hero.id", primary_key=True
    )

    salary: int
    is_disabled: bool = False

class TeamBase(SQLModel):
    id: Optional[int]
    name: str
    headquarters: str

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

class HeroBase(SQLModel):
    id: Optional[int]
    name: str
    secret_name: str
    age: Optional[int] = None

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

class HeroMembership(HeroBase):
    salary: int
    is_disabled: bool

class TeamDetail(TeamBase):
    heroes: List[HeroMembership] = []

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def fetch_team(session, id: int = 1) -> TeamDetail:
    with Session(engine) as session:
        query = (
            select(Team)
            .join(Team.heroes)
            .where(Team.id == id)
            .options(joinedload(Team.heroes))
        )
        """
        NOTE: the SQL query generated is below:

        SELECT team.*, hero.* FROM team
        JOIN membership AS membership_1 ON team.id = membership_1.team_id
        JOIN hero ON hero.id = membership_1.hero_id
        LEFT OUTER JOIN (membership AS membership_2
          JOIN hero AS user_1 ON user_1.id = membership_2.hero_id)
        ON team.id = membership_2.team_id
        WHERE team.id = :team_id

        TODO: how to fetch additional fields from the link table since it is clearly accessed anyways?
        """
        team_details = session.exec(query).first()
        Team.update_forward_refs()
        return team_details

def create_heroes():
    with Session(engine) as session:
        team = fetch_team(engine)
        print(team)

def main():
    create_db_and_tables()
    create_heroes()

if __name__ == "__main__":
    main()

Description

• Create Hero model
• Create Team model
• Create link_model, Membership with some additional fields
• Try to fetch a particular team with list of heroes, incl. additional membership field(s) per hero

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

Python 3.10.9

Additional Context

Here's the SQL query generated that fetches the response correctly EXCEPT for additional membership field(s) per hero:

        SELECT team.*, hero.* FROM team
        JOIN membership AS membership_1 ON team.id = membership_1.team_id
        JOIN hero ON hero.id = membership_1.hero_id
        LEFT OUTER JOIN (membership AS membership_2
            JOIN hero AS user_1 ON user_1.id = membership_2.hero_id)
        ON team.id = membership_2.team_id
        WHERE team.id = :team_id

[Pk13055]

PS - I do not want to create manual team_link, hero_link fields in the schema (OR at least avoid unless the above is impossible otherwise)

[Pk13055]

@tiangolo Do you have any comments on how to possible achieve this?

[JakNowy]

query = (
            select(Team)
            .join(Membership, Membership.team_id == Team.id)
            .options(joinedload(Team.heroes))
            .where(Team.id == id)
        )

should work for you