Many-many relationship challenges when the link table only uses one of the FK as a primary

[MLDERES]

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 Band_Member2(SQLModel,table=True):
    __table_name__ = "band_member"
    status: str = Field( default=None,description="Member Status code", sa_column_kwargs={"name":"member_status_code"})
    band_id: int = Field(
        default=None,
        description="Band ID",
        foreign_key="band.band_id",
    )
    member_id: int = Field(
        default=None,
        description="Member ID",
        foreign_key="person.person_id",
        primary_key=True,
    )
    join_date: Optional[datetime] = Field(
        default=None, description="Date the band member joined the band"
    )

class Person2(SQLModel, table=True):
    __table_name__ = "person"
    id: int = Field(default=None, primary_key=True)
    first_name: str = Field(default=None,sa_column_kwargs={"name":"first_name"})
    last_name: str = Field(default=None,sa_column_kwargs={"name":"last_name"})

    band_links: List["Band_Member2"] = Relationship(back_populates="member", link_model=Band_Member2)

class Band2(SQLModel, table=True):
    __table_name__ = "band"
    id: int = Field(default=None, primary_key=True)
    name: str = Field(default=None,sa_column_kwargs={"name":"band_name"})

    band_members: List["Band_Member2"] = Relationship(back_populates="band", link_model=Band_Member2)


@app.get("/bands", response_model=List[Band])
def read_band(*, session: Session = Depends(get_session)):
    return session.exec(select(Band)).all()

Description

• Create a Band model
• Create a Person model
• Create the link table between the person model and band model
• Query for all Bands with members

Get an error "Could not determine join condition between parent/child tables on relationship Person.band_links"

I've narrowed the code down to the simplest possible definition (I've had to rename the tables and objects not to conflict with other parts of my project). I feel like this matches the Team/Hero example in the tutorial exactly, but I have spent a few hours re-reading the tutorial, searching on-line and trying every conceivable combination without any progress. I'm sure it's something simple, but I sure could use some help.

Operating System

Linux

Operating System Details

No response

SQLModel Version

SQLModel 0.0.8

Python Version

Python 3.11.2

Additional Context

I didn't include all the details to create the database and all, but essentially the database has the following tables

Band
band_id: int PK
...

Person
person_id: int PK

Band_Member
Member_ID: int PK, FK1 (links to Person.person_id)
Band_ID: FK2 (links to Band.band_id)
...

[dongsupkim-onepredict]

I feel like that primary keys in each table should be like this:

class Band_Member2(SQLModel,table=True):
    __table_name__ = "band_member"
    status: str = Field( default=None,description="Member Status code", sa_column_kwargs={"name":"member_status_code"})
    band_id: int = Field(
        default=None,
        description="Band ID",
        foreign_key="band.id", ## here band.band_id -> band.id
    )
    member_id: int = Field(
        default=None,
        description="Member ID",
        foreign_key="person.person_id", ## here person.person_id -> person.idd
        primary_key=True,
    )
    join_date: Optional[datetime] = Field(
        default=None, description="Date the band member joined the band"
    )

[YuriiMotov]

Also, to specify table name you should use __tablename__ instead of __table_name__