Return a Column class for relationship attributes that require it

[ohmeow]

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

class UpsertByModelMixin(SQLModel):
    created_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    created_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': [created_by_id] })
    
    updated_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    updated_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': [updated_by_id] })


class Team(UpsertByModelMixin, SQLModel, table=True,):
    __tablename__ = 'teams'
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=500)

Description

Use models such as above will result in the following error because the foreign_keys argument expects a Column (not a SQLModel Field).

| sqlalchemy.exc.ArgumentError: Column expression expected for argument 'foreign_keys'; got FieldInfo(default=PydanticUndefined, extra={'exclude': None, 'include': None}).

As such, its impossible right now for anyone to deploy classes with multiple FKs to the same identity and/or use custom named foreign key columns.

Wanted Solution

To be able to specify foreign keys to the same related entity and/or name my fk columns however I choose.

Wanted Code

Just like above

Alternatives

No response

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.5

Additional Context

No response

[ohmeow]

Maybe something like this would work ...

owner_id : Optional[int] = Field(foreign_key="users.id")
owner: Optional["User"] = Relationship( sa_relationship_kwargs={ 'foreign_keys': owner_id.as_sa_column })

[pmarcelll]

I've encountered the same issue and after reading this SQLAlchemy guide I've managed to work around it.

Based to the guide your code should look like this:

class UpsertByModelMixin(SQLModel):
    created_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    created_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': '[UpsertByModelMixin.created_by_id]' })
    
    updated_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    updated_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': '[UpsertByModelMixin.updated_by_id]' })

[alextremblay]

it would be nice if Relationship had a local_key param to handle this, to avoid a lot of boilerplate.

Ex, instead of

from sqlmodel import Field, Relationship, SQLModel

class User(SQLModel, table=True):
    ...

class Example(SQLModel, table=True):
    id: int = Field(primary_key=True)
    adminc: str | None = Field(default=None, foreign_key="users.username")
    techc: str | None = Field(default=None, foreign_key="users.username")
    adminc_alt: str | None = Field(default=None, foreign_key="users.username")
    techc_alt: str | None = Field(default=None, foreign_key="users.username")

    admin_contact: User | None = Relationship(sa_relationship_kwargs={"foreign_keys": 'Network.adminc'})
    tech_contact: User | None = Relationship(sa_relationship_kwargs={"foreign_keys": 'Network.techc'})
    admin_contact_alt: User | None = Relationship(sa_relationship_kwargs={"foreign_keys": 'Network.adminc_alt'})
    tech_contact_alt: User | None = Relationship(sa_relationship_kwargs={"foreign_keys": 'Network.techc_alt'})

It would be nice to be able to write

from sqlmodel import Field, Relationship, SQLModel

class User(SQLModel, table=True):
    ...

class Example(SQLModel, table=True):
    id: int = Field(primary_key=True)
    adminc: str | None = Field(default=None, foreign_key="users.username")
    techc: str | None = Field(default=None, foreign_key="users.username")
    adminc_alt: str | None = Field(default=None, foreign_key="users.username")
    techc_alt: str | None = Field(default=None, foreign_key="users.username")

    admin_contact: User | None = Relationship(local_key='adminc')
    tech_contact: User | None = Relationship(local_key='techc')
    admin_contact_alt: User | None = Relationship(local_key='adminc_alt')
    tech_contact_alt: User | None = Relationship(local_key='techc_alt')