Access Relationship (from table model) from Read Model

[LeonardoGentile]

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 Field, SQLModel, Relationship


# M2M
class HeroPowerLink(SQLModel, table=True):
    hero_id: Optional[int] = Field(
        default=None, foreign_key="hero.id", primary_key=True
    )
    power_id: Optional[int] = Field(
        default=None, foreign_key="power.id", primary_key=True
    )


class PowerBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    description: str


class Power(PowerBase, table=True):
    heroes: List["Hero"] = Relationship(back_populates="powers", link_model=HeroPowerLink)


# =====

class HeroBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)


class Hero(HeroBase, table=True):
    powers: List["Power"] = Relationship(back_populates="heroes", link_model=HeroPowerLink)


class HeroCreate(HeroBase):
    type: str


class HeroRead(HeroBase):
    id: int
    # HOW TO ACCESS `powers` HERE?

Description

Following from the tutorial I've created a similar model structure.
The model Power and Hero are linked via and M2M model HeroPowerLink.

As mentioned this kind of relationship fields should be added on both sided in the table model, that is Hero and Power.

The problem is that now from my HeroRead (what's returned to the user) I want to include the field powers, that is a list of Powers objects.

In this case HeroRead inherits directly form HeroBase (the documentation discourage to inherit from table models) and of course it can't see the powers field.
If I declare the powers in the base model I get

sqlalchemy.exc.InvalidRequestError: Mapper 'mapped class Hero->hero' has no property 'episodes'

I think I'm missing something here.
How can I access powers from the HeroRead?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.8.9

Additional Context

No response

[byrman]

I guess you already tried this?

class HeroRead(HeroBase):
    id: int
    powers: List[Power]

But there is a caveat: by the time the powers attribute is accessed in your code, your session might already be closed. You can solve this by loading powers up front. This worked for me:

from sqlalchemy.orm import joinedload

statement = select(Hero).options(joinedload(Hero.powers))
heroes = session.exec(statement).unique().all()

[antont]

Also something like this is an alternative solution, don't know how to say it with the link table though:

class Power(PowerBase, table=True):
    heroes: List["Hero"] = Relationship(back_populates="powers", 
        sa_relationship_kwargs={"primaryjoin": "Power.hero_id==Hero.id", "lazy": "joined"})

Such SQLAlchemy relationship & link table seems to be discussed in #89 for other reasons.

[byrman]

Good to know, thanks! But Power has no hero_id attribute, the relationship is m2m, so I guess your code snippet needs some modification?

[byrman]

Ah, you already mentioned that, sorry, my bad.

[byrman]

Yes, the solution suggested by @antont also works:

class Power(PowerBase, table=True):
    heroes: List["Hero"] = Relationship(
        back_populates="powers",
        link_model=HeroPowerLink,
        sa_relationship_kwargs={
            "primaryjoin": "Hero.id==HeroPowerLink.hero_id",
            "secondaryjoin": "HeroPowerLink.power_id==Power.id",
        },
    )