How to add additional filters on default result of relationship with link model?

[20-buck-spin]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

class HeroTeamLink(SQLModel, table=True):
    team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)
    hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)
    is_training: bool = False


class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: list["Hero"] = Relationship(back_populates="teams", link_model=HeroTeamLink)


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

    teams: list[Team] = Relationship(back_populates="heroes", link_model=HeroTeamLink)

Description

Hello,

I am trying to follow the tutorial of SQLModel (huge love towards the project!). While going through the many-to-many section, If found an example of extending the HeroTeamLink model with the added field is_training.

I am trying to understand is there any way to adapt the "teams" field (relationship) on the hero class such that it always provides a list of all teams where is_training is True in the LinkTable.

The tutorial shows how we can directly interact with the link models and change the value of fields. My goal is to easily access all the teams a hero is a part of where the hero has completed the training. I tried looking at the SqlAlchemy docs... but just got hopelessly lost. Any help would be much appreciated.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.19

Python Version

3.12

Additional Context

No response

[20-buck-spin]

After searching for some time, I seem to have found a solution. The trick is to use the sa_relationship_kwargs in the following way

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

    teams: list[Team] = Relationship(back_populates="heroes", link_model=HeroTeamLink, 
               sa_relationship_kwargs={"primaryjoin": 'and_(HeroTeamLink.hero_id==Hero.id, HeroTeamLink.is_training==True)'
         )