SQLModel "Person" with multiple attributes of the same SQLModel "Address" without a list

[mariusbrinkmann]

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

from typing import Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine

class Address(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    country: str
    city: str

class Person(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    home_address: Address # How to specify the relationship here?
    work_address: Address # How to specify the relationship here?

if __name__ == "__main__":
    home_address = Address(country="US", city="Los Angeles")
    work_address = Address(country="US", city="New York City")
    person = Person(
        home_address=home_address,
        work_address=work_address
    )

Description

• Create an Address model that should be a table
• Create a Person model that should be a table and has two attributes home_address and work_address both of the same type Address
• Creating an instance of Person should
  • add one row to the person table
  • add two rows to the address table representing the Person instance's home_address and work_address
  • somehow link the two rows in the address table with the row in the person table

Executing the minimum working example does not work as the relationship between the to tables is missing. What's the correct approach to create models like this if I want to keep two separate attributes instead of addresses: List[Address]? Is a link table with a type column (home or work) the correct approach?

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

3.10.11

Additional Context

No response

[dangets]

I would be interested if there is a "more native" way to do this, but I got it to work by using sql alchemy primaryjoin

from typing import Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select

class Address(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    country: str
    city: str

class Person(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    home_address_id: int = Field(foreign_key="address.id")
    home_address: Address = Relationship(sa_relationship_kwargs={"primaryjoin": lambda: Person.home_address_id == Address.id})

    work_address_id: int = Field(foreign_key="address.id")
    work_address: Address = Relationship(sa_relationship_kwargs={"primaryjoin": lambda: Person.work_address_id == Address.id})



engine = create_engine("sqlite://", echo=True)  # in-mem database
SQLModel.metadata.create_all(engine)

if __name__ == "__main__":
    home_address = Address(country="US", city="Los Angeles")
    work_address = Address(country="US", city="New York City")
    person = Person(
        home_address=home_address,
        work_address=work_address
    )

    with Session(engine) as session:
        session.add(person)
        session.commit()

        print("--- all addresses ----------")
        for a in session.exec(select(Address)):
            print(a)

        print("--- all people ----------")
        for p in session.exec(select(Person)):
            print(p)
            print(p.home_address)
            print(p.work_address)