I'm developing a FastAPI application, a file manager system, and I'm using PostgreSQL as the database. To define database entity models in my code, I previously used SQLAlchemy, but now I switched to SQLModel, which is a wrapper for SQLAlchemy.
This is the SQLAlchemy version of the entity model. I defined for 'Folder' entity, which has a self-referencing relationship, it worked well, didn't return any error.
class Folder(Base):
__tablename__ = "folder"
__table_args__ = (
ForeignKeyConstraint(
["account_id", "parent_id"], ["folder.account_id", "folder.folder_id"]
),
)
account_id = mapped_column(Integer, primary_key=True)
folder_id = mapped_column(Integer, primary_key=True)
parent_id = mapped_column(Integer)
name = mapped_column(String)
parent_folder = relationship(
"Folder", back_populates="child_folders", remote_side=[account_id, folder_id]
)
child_folders = relationship("Folder", back_populates="parent_folder")
I converted above model into the SQLModel format; I used:
from typing import List, Optional
from sqlmodel import SQLModel, Field, Column, Relationship
from uuid import UUID, uuid4
class Folder(SQLModel, table=True):
__tablename__ = "folder"
...
folder_id: int = Field(primary_key=True)
parent_id: int = Field()
...
parent_folder = Relationship(
back_populates="child_folders",
)
child_folders = Relationship(back_populates="parent_folder", sa_relationship_kwargs={"primaryjoin":"folder.folder_id== folder.folder_id"})
When I run the FastAPI application, after this, it reports below error in the terminal
Could not determine join condition between parent/child tables on relationship Folder.children - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
I think the error is about the self-referencing relationship (child folders field). What is a solution for this? How do I correct the child folders field?
