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/