Back references using a one to many link table

[BVengo]

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 Column, Field, Relationship, SQLModel, VARCHAR


class Error(SQLModel, table=True):
    error_id: Optional[int] = Field(primary_key=True)
    error_name: str = Field(sa_column=Column("error_name", VARCHAR(255), nullable=False))

class File(SQLModel, table=True):
    file_id: Optional[int] = Field(primary_key=True)
    file_name: str = Field(sa_column=Column("file_name", VARCHAR(255), nullable=False))

class Action(SQLModel, table=True):
    action_id: Optional[int] = Field(primary_key=True)
    action_name: str = Field(sa_column=Column("action_name", VARCHAR(255), nullable=False))

class FileError(SQLModel, table=True):
    file_id: Optional[int] = Field(primary_key=True, foreign_key="file.file_id")
    error_id: Optional[int] = Field(primary_key=True, foreign_key="error.error_id", unique=True)

class ActionError(SQLModel, table=True):
    action_id: Optional[int] = Field(primary_key=True, foreign_key="action.file_id")
    error_id: Optional[int] = Field(primary_key=True, foreign_key="error.error_id", unique=True)

Description

In my code, I have a generic 'Error' table that can be used by multiple systems to store details about errors. In my reproducible example, I used 'File' and 'Action' as example systems that may throw errors (and shall refer to them as 'system' from now on).

Each error can only correspond to a single file or action, however each system could have multiple errors. To maintain the generic nature of the error table I've created 1-many link tables FileError and ActionError.

• How can I correctly set up their relationships? In case it helps, I don't need to be able to navigate upwards from the error (e.g. error.file), but I do need to be able to navigate downwards (file.errors).
• Does back_populates always return a list when using a link table? I ask this because my systems will contain a list of errors (which I can easily set up since it directly reflects the use-case from the docs), however each error should only contain a single system.

I'm hoping I was clear enough, but let me know if I missed anything.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.11.4

Additional Context

No response

[akaymd]

I think you can solve it by reading the Define Relationships Attributes .