Relationship from Model Data and inheritance

[Franksign]

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 SurveyDTO(SQLModel):
    responses: List[ResponsesDTO] = []
    # other fields.. 

class ResponsesDTO(SQLModel):
    code: int
    response: str

class SurveyTable(SurveyDTO, table='True'):
    id: Optional[int] = Field(default=None, primary_key=True)
    # how to manage relationship from DTO?

class ResponsesTable(ResponsesDTO, table='True'):
    id: Optional[int] = Field(default=None, primary_key=True)
    # how to manage relationship from DTO?

In FastAPI:

@app.post(endpoint_paths.SURVEY)
def post_survey(session: Session = Depends(get_session),
                           survey: SurveyDTO= Body(..., embed=True)) -> Response:
                 # save the survey

Description

I am trying to create a a one to many relationship through inheritance from a model class to a table class.
I don't understand how to create the relationship with the List[ResponsesDTO] in the table without duplicating code.
Maybe I am missing something?

Thank you for your help :)

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.08

Python Version

3.8

Additional Context

Seems related to #18 and #469

[Kludex]

If you can provide an MRE, I can try to help. 👀

[phi-friday]

it works

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

engine = create_engine('sqlite://',echo=True)

class SurveyDTO(SQLModel):
    ...
    # other fields.. 

class ResponsesDTO(SQLModel):
    code: int
    response: str

class SurveyTable(SurveyDTO, table=True):
    __tablename__: ClassVar[str] = 'survey_table'

    id: Optional[int] = Field(default=None, primary_key=True)
    # how to manage relationship from DTO?
    responses: List["ResponsesTable"] = Relationship()

class ResponsesTable(ResponsesDTO, table=True):
    __tablename__: ClassVar[str] = 'responses_table'

    id: Optional[int] = Field(default=None, primary_key=True)
    # how to manage relationship from DTO?
    survey_id: Optional[int] = Field(default=None, foreign_key='survey_table.id')
    survey: SurveyTable = Relationship()

def main():
    with Session(engine) as session:
        SQLModel.metadata.create_all(session.connection())
        session.commit()

        survey = SurveyTable()
        response1 = ResponsesTable(code=1, response='test1')
        response2 = ResponsesTable(code=1, response='test2')
        response3 = ResponsesTable(code=1, response='test3')
        survey.responses = [response1, response2, response3]
        session.add(survey)
        session.commit()

    with Session(engine) as session:
        survey = session.get(SurveyTable, 1)
        print(survey)
        if survey is not None:
            print(survey.responses)

if __name__ == '__main__':
    main()

[Franksign]

This solves the problem from the SQLModel side. From FastAPI side the SurveyDTO and the list come as JSON payload from a POST request.

@app.post(endpoint_paths.SURVEY)
def post_survey(session: Session = Depends(get_session), survey: SurveyDTO= Body(..., `embed=True))

We could say that the object in the POST request should bea a SurveyTable but in this way I am exposing db data to the outside (and to documentation).

If I define the list in SurveyDTO and ovverride in SurveyTable I get ValueError: The field responses has no matching SQLAlchemy type. Is there a way to map a field from DTO to a table field? Or I need to just duplicate the field in the parent.

Edit: added the additional context with related PR and issue.

Thank you guys!

[YuriiMotov]

I think your endpoint should look like this (accept id of survey and list of responses):

@app.post("/survey/{survey_id}/responses")
def post_survey(
    survey_id: int,
    session: Session = Depends(get_session),
    survey_responses: List[ResponsesDTO] = Body(..., embed=True),
):
    survey = session.get(SurveyTable, survey_id)
    survey.responses = [
        ResponsesTable.model_validate(survey_response)
        for survey_response in survey_responses
    ]
    ...