Unable to get a nested (join) response on 1:1 relationship

[AthreyVinay]

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

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

class Design(SQLModel, table=True):
    __tablename__: str  = 'DESIGN'
    DESIGN: Optional[str] = Field(default=None, primary_key=True)
    project: Optional["AdvancedProject"] = Relationship(
         sa_relationship_kwargs={'uselist': False}
    )

class AdvancedProject(SQLModel, table=True):
    __tablename__: str  = 'ADVANCEDPROJECT'
    PROJECT: Optional[str] = Field(default=None, primary_key=True)
    MASTERDESIGN: Optional[str] = Field(default=None, foreign_key="DESIGN.DESIGN")
    MASTERDESIGN_MODEL: Optional[Design] = Relationship(sa_relationship_kwargs={'uselist': False}, back_populates="project")

Description

I have 2 models -

1. ADVANCEDPROJECT
2. DESIGN

The ADVANCEDPROJECT has a field called MASTERDESIGN which is a foreign key to DESIGN model. I have shown in the example code the way I have coded the 2 models. Note that the DESIGN model has no back relationship to the ADVANCEDPROJECT model i.e it os only one way -> from ADVANCEDPROJECT to DESIGN. When I query for a ADVANCEDPROJECT project = db.query(AdvancedProject).filter(AdvancedProject.PROJECT == item_id).first() , I expected I would get the nested DESIGN fields too. But in turn I only see the string value of MASTERDESIGN field. Please see below the reponse:

{
    "PROJECT": "0000169868185008210",
    "MASTERDESIGN": "0640767955185008210",
}

But I was expecting:

{
    "PROJECT": "0000169868185008210",
    "MASTERDESIGN": {
         "DESIGN": "0640767955185008210"
      }
}

Can someone please point out my mistake / learning? Thanks in advance.

Operating System

Windows

Operating System Details

Windows Server 2019

SQLModel Version

0.0.6

Python Version

3.10.2

Additional Context

No response

[AthreyVinay]

Also - just observed that this is creating a infinite ADVANCEDPROJECT->DESIGN->ADVANCEDPROJECT->DESIGN.... (so on) loop. Definitely not what I intend.

[AthreyVinay]

The current workaround (contains errors and I think is not correct) that I'm doing is:

class AdvancedProject(SQLModel, table=True):
    __tablename__: str  = 'ADVANCEDPROJECT'
    PROJECT: Optional[str] = Field(default=None, primary_key=True)
    MASTERDESIGN: Optional[Design] = Field(default=None, foreign_key="DESIGN.DESIGN")
    
    @property
    def design(self):
        return object_session(self).get(Design, self.MASTERDESIGN)

And then setting the value for MASTERDESIGN like:

project = db.query(AdvancedProject).filter(AdvancedProject.PROJECT == item_id).first()
project.MASTERDESIGN = project.design

I'm getting the desired results, but I get the following error in my console:
pyodbc.ProgrammingError: ('Invalid parameter type. param-index=0 param-type=Design', 'HY105')

[Fanna1119]

the docs explain here how to read relationships.
https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/read-relationships/

[YuriiMotov]

Relationship properties are not serialized to prevent loops.
The solution is explained in Multiple Models with FastAPI.
You need to create one more model:

class AdvancedProjectOutput(SQLModel):
    PROJECT: str
    MASTERDESIGN_MODEL: Optional[Design]

And use it for result validation and serialization:

        proj_db = session.get(AdvancedProject, "project")
        proj_output = AdvancedProjectOutput.model_validate(
            proj_db, from_attributes=True
        )
        print(proj_output.model_dump())

Output:

{
    "PROJECT": "project",
    "MASTERDESIGN_MODEL": {"DESIGN": "design"},
}

Runnable code example in details:

from typing import Optional

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

class Design(SQLModel, table=True):
    __tablename__: str = "DESIGN"
    DESIGN: Optional[str] = Field(default=None, primary_key=True)
    project: Optional["AdvancedProject"] = Relationship(
        sa_relationship_kwargs={"uselist": False}
    )

class AdvancedProject(SQLModel, table=True):
    __tablename__: str = "ADVANCEDPROJECT"
    PROJECT: Optional[str] = Field(default=None, primary_key=True)
    MASTERDESIGN: Optional[str] = Field(default=None, foreign_key="DESIGN.DESIGN")
    MASTERDESIGN_MODEL: Optional[Design] = Relationship(
        sa_relationship_kwargs={"uselist": False}, back_populates="project"
    )

class AdvancedProjectOutput(SQLModel):
    PROJECT: str
    MASTERDESIGN_MODEL: Optional[Design]

def main():
    engine = create_engine("sqlite:///", echo=True)
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        design = Design(DESIGN="design")
        proj = AdvancedProject(PROJECT="project", MASTERDESIGN_MODEL=design)
        session.add(proj)
        session.commit()

    with Session(engine) as session:
        proj_db = session.get(AdvancedProject, "project")
        proj_output = AdvancedProjectOutput.model_validate(
            proj_db, from_attributes=True
        )
        assert proj_output.model_dump() == {
            "PROJECT": "project",
            "MASTERDESIGN_MODEL": {"DESIGN": "design"},
        }

if __name__ == "__main__":
    main()