How to build a relationship with yourself

[Leewinner1]

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 sqlmodel import SQLModel, Field, Relationship
class Category(SqlModel, table=True):
    __tablename__ = 'tb_category'
    id: int = Field(default=None, primary_key=True)
    name: str = Field(max_length=30)
    p_id: int = Field(default=None,foreign_key='tb_category.id')
    sort: int = Field(default=0)
    child :'Category' = Relationship(back_populates="Category")

# I created a p_id field in the Category model class, associated with its own id.
# And I also define a child to have a relationship with the Category itself
# What I want to do is implement Category.p_id.name to find my own "name"

#############This is possible with flask-sqlalchemy, but not with sqlmodel
class Category(db.Model):
    __tablename__ = 'tb_category'
    id = db.Column(db.Integer,primary_key =True)
    name = db.Column(db.String(32))
    p_id = db.Column(db.Integer,db.ForeignKey('tb_category.id'))
    child = db.relationship('Category')

Description

i created a p_id field in the Category model class, associated with its own id.
And I also define a child to have a relationship with the Category itself
What I want to do is implement Category.p_id.name to find my own "name"

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.11

Python Version

3.12

Additional Context

No response

[nayaverdier]

If p_id is meant to be a parent_id on the other side of the child relationship, you can do something like this (assuming a parent category can have multiple child categories):

    p_id: int | None = Field(None, foreign_key="tb_category.id")

    parent: Optional["Category"] = Relationship(back_populates="children", sa_relationship_kwargs={"remote_side": lambda: Category.id})
    children: list["Category"] = Relationship(back_populates="parent")

Then you can use some_category.parent.name.

[DurandA]

In addition to @nayaverdier answer, here is another example using a one-to-many relationship to create a tree:

class Node(SQLModel, table=True):
    uuid: UUID = Field(
        default_factory=uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )

    parent_uuid: UUID | None = Field(
        foreign_key='node.uuid', # notice the lowercase "n" to refer to the database table name
        default=None,
        nullable=True
    )
    parent: Optional['Node'] = Relationship(
        back_populates='children',
        sa_relationship_kwargs=dict(
            remote_side='Node.uuid' # notice the uppercase "N" to refer to this table class
        )
    )
    children: list['Node'] = Relationship(back_populates='parent', sa_relationship_kwargs={"lazy": "joined", "join_depth": 2})