How to access a parent model's relationship's attributes

[yudjinn]

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 UserBase(Base):
    username: str
    in_game_name: str
    discord_name: Optional[str] = Field(default=None)
    is_active: Optional[bool] = Field(default=True)
    is_superuser: Optional[bool] = Field(default=False)

    company: Optional[CompanyUser] = Relationship(back_populates="company")


class UserRead(UserBase):
    rank: str = UserBase.company.rank

---------
class CompanyUser(SQLModel, table=True):
    """
    Link Table to store ranks between users and a company
    """

    company_id: uuid.UUID = Field(foreign_key="company.id", primary_key=True)
    user_id: uuid.UUID = Field(foreign_key="user.id", primary_key=True)

    rank: str

    company: "CompanyBase" = Relationship(back_populates="members")
    user: "UserBase" = Relationship(back_populates="company")


class CompanyBase(Base):
    name: str
    logo_id: Optional[uuid.UUID] = Field(default=None, foreign_key="file.id")

    members: List[CompanyUser] = Relationship(back_populates="user")

Description

Erroring on UserRead>rank: UserBase has no attribute "company".

Effectively, I'm unsure how to access the parent model's relationships.

Operating System

Linux, Windows

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.7

Additional Context

Trying to follow this guide on link tables with attributes: https://sqlmodel.tiangolo.com/tutorial/many-to-many/link-with-extra-fields/

[rodg]

UserBase.company isn't a class constant so you won't be able to access it like that. As for the SQLModel part, the docs mention not to inherit tables (which I'm assuming UserBase is here since I'm not sure what Base is).

If Base isn't some SQLAlchemy base and is a pydantic model, I'm not sure relationship will work at all. AFAIK the SQLModel Relationship works along the lines of SQLAlchemy relationships, so non-table classes which inherit SQLModel might not make much sense of Relationship b/c they are closer to purely pydantic models. Getting pulled away so I'll try and update this later, but look into pydantic validators (specifically pre-validation) if you want to try and access relationship attributes inside a pydantic model.

[yudjinn]

UserBase is not a table. Neither is Base. Only User has the table=True. It's defintion was essentially inheriting from UserBase with a table=True and passing on the body. It just seems weird that I have to redefine columns over and over again for each function (read, create, etc) rather than having a base and inheriting. I tried using this from some other gh issues:
@property def rank(self) -> Optional[str]: return self.company.rank

but that also doesnt populate in the return object

[rodg]

Ok, so you'd want (and potentially need) to have the relationship on the table not the data model, so User would need to have that relationship. Your UserRead would then just have rank as a str with whatever default value you want. You can then use a pydantic pre-validator to populate rank when the UserRead object is created by using an alias with company to have that CompanyUser passed into the validator so you can process and grab the rank attribute you want.

At least, this is what I've done in the past for this. In your case I believe it would look something like this, but I'd need your full example to be more specific:

class UserRead(UserBase):
    company: str = Field(..., alias="rank")

    @validator("company", pre=True)
    def get_rank(cls, company: CompanyUser) -> str:
        return company.rank

    class Config:
        allow_popoulation_by_field_name: True


class User(UserBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    company: Optional[CompanyUser] = Relationship(back_populates="company")

I'd like to add though that I've not confirmed this works on SQLModel, I believe it should though. Not sure if there is a better way or not.

[yudjinn]

I guess I'm just lost on the how. The docs say that you should make a base data class and have your actual table inherit from that, and never have things inherit from your table. but if that's true then how do you setup a read model to get relationships if they cant see the relationships?? further, especially with a user, only the table model will have the password (intended), but the read model can literally never see the blog posts associated with a user, for example, since that is in a parallel object. Because when the docs talk about relationships, they show it on the base object (not table) and have read's inherit from that

[rodg]

class UserBase(SQLModel):
    name: str

class UserRead(UserBase):
    company: str = Field(..., alias="rank")

    @validator("company", pre=True)
    def get_rank(cls, company: CompanyUser) -> str:
        return company.rank

    class Config:
        allow_popoulation_by_field_name: True


class User(UserBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    password: str
    company: Optional[CompanyUser] = Relationship(back_populates="company")

@app.get("/user/{id}", response_model=UserRead)
def get_user(/* db connection */, id: str):
    ...
    ...
    return /* some object of type User */

Assuming you are using SQLModel with FastAPI, when you return data you specify which model you want that data to fit into. In this case, if you get a User from the DB it'd have fields id, password, name, and company. By placing a company attribute on UserRead, and specifying that type as your response model, the validator should be run when you return your User object. In the validator we have direct access to the CompanyUser object that we specified in the relationship.

You can replace company with whatever, if you have a list of blog post objects created by a relationship you just need to process the list in that validator. It works off of attribute names so all you need is for your attribute in the read model to match the attribute you create for the relationship (so company in this example). Again I've not fully written this with SQLModel and tested it, but I know the underlying frameworks (SQLAlchemy and Pydantic) can do this. I believe this general structure should work still, but if I find out otherwise I'll comment again.

Because when the docs talk about relationships, they show it on the base object (not table) and have read's inherit from that

Where in the docs is this? I only see relationships being put on tables not data classes (since as I said it probably won't work at all on data classes).

[traviscook21]

Stumbled on to this comment and attempted to get it working and was struggling.

There were two issues with the sample code, both on the UserRead's Config:

class UserRead(UserBase):
    company: str = Field(..., alias="rank")

    @validator("company", pre=True)
    def get_rank(cls, company: CompanyUser) -> str:
        return company.rank

    class Config:
        allow_population_by_field_name = True

That should work. With the code as is, the config wasn't taking and it required alias="company" which doesn't allow for the mapping necessary.

[traviscook21]

While this example works just fine if you're attempting to access a single attribute on the parent, it breaks down when you need to access two different attributes.

Using the examples from the documentation:

class TeamBase(SQLModel):
    name: str
    headquarters: str


class Team(TeamBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    heroes: List["Hero"] = Relationship(back_populates="team")

class TeamRead(TeamBase):
    id: int


class HeroBase(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    team: Optional[Team] = Relationship(back_populates="heroes")

If I wanted the HeroRead response to look like this:

{
    "id": number,
    "name": string,
    "secret_name": string,
    "age" number,
    "team_id": number,
    "team_name": string,
}

It's not clear how to define the HeroRead class:

class HeroRead(HeroBase):
    id: int
    team: TeamRead = Field(alias="team_name")
    team: TeamRead = Field(alias="team_id")

    @validator("team", pre=True)
    def get_team_id(cls, team: TeamRead) -> int:
        return team.id

    @validator("team", pre=True)
    def get_team_name(cls, team: TeamRead) -> str:
        return team.name

    class Config:
        allow_population_by_field_name = True

The above clearly won't work because I have two attributes called team and two validators for the same attribute (even with different aliases). As a result, the output of get_team_id gets passed to get_team_name and the eventual error is that AttributeError: 'int' object has no attribute 'name'

[rodg]

So this starts to get kind of janky, but you can get HeroRead to work. It does feel like a misuse of the validators though, so if there is a better way to do this I'd love to hear it.

class HeroRead(HeroBase):
    id: int
    team_name: Optional[str] = None    # default value matters here
    team: int = Field(alias="team_id")

    @validator("team", pre=True)
    def get_team_id(cls, team: TeamRead, values: Dict) -> int:
        values['team_name'] = team.name
        return team.id

    class Config:
        allow_population_by_field_name = True

From the pydantic docs, by adding values to the validator we get a dictionary of previously set/validated attributes. Since we defaulted/set team_name to null before the validator gets run for team (even though this is a pre-validator), team_name will be in values and we can then modify it. If you don't give team_name a default of something it won't be in the values dictionary, so that step is important.

There is also the option of nesting a TeamRead into the HeroRead as mentioned in the docs, but I sometimes needed to do what I put above to format things in a more helpful way.

[traviscook21]

Got it, that all makes sense.

Agreed it feels janky and a perversion of validators. Inherently, validators are used to "validate" data but really we're using them to mutate / transform here. It's not inherently bad, just a misnomer. Marshmallow lets you associate Fields with functions or methods to do this kind of transformation which is a mental model that feels more appropriate, but isn't supported in sqlmodel or pydantic today (AFAIK).

Also agreed that a better option is to nest the TeamRead object inside of HeroRead but sometimes that's not possible because of backwards compatibility and general API design.

Long term, SQLModel should have a better way to support this type of behavior but this feels like an acceptable workaround for now.

[LeonardoGentile]

I've tried to implement the above solution and I've got:

Parent instance <MyModel at 0x109ae3580> is not bound to a Session; lazy load operation of attribute 'example_field' cannot proceed 

I've tried to add lazy='subquery' to the relationship (I've tried both sides) but it seems an attribute not accepted by SqlModel.

I'm new to sqlmodel and fastapi and I was surprised that this use case is so difficult to implement.
I can't be the only one that need to flatten the related instances into the parent 🤷‍♂️

Any advice to fix at least my error for the time being?

[lovetoburnswhen]

I've tried to implement the above solution and I've got:

Parent instance <MyModel at 0x109ae3580> is not bound to a Session; lazy load operation of attribute 'example_field' cannot proceed 

I've tried to add lazy='subquery' to the relationship (I've tried both sides) but it seems an attribute not accepted by SqlModel.

I'm new to sqlmodel and fastapi and I was surprised that this use case is so difficult to implement. I can't be the only one that need to flatten the related instances into the parent 🤷‍♂️

Any advice to fix at least my error for the time being?

You need to pass lazy='subquery' to sa_relationship_args/sa_relationship_kwargs, or just pass in the entire sa_relationship object. Also please share your code, it's difficult to tell which example you're referring to

[LeonardoGentile]

@lovetoburnswhen I solved thanks to your comment 👍
I was referring to the latest proposed solution by rodg

class HeroRead(HeroBase):
    id: int
    team_name: Optional[str] = None    # default value matters here
    team: int = Field(alias="team_id")

    @validator("team", pre=True)
    def get_team_id(cls, team: TeamRead, values: Dict) -> int:
        values['team_name'] = team.name
        return team.id

    class Config:
        allow_population_by_field_name = True

I will publish my case so it might maybe help someone.
My use case is very similar, and indeed by passing sa_relationship_kwargs={'lazy': 'subquery'} as suggested fixed the problem:

class CharacterTypeBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


class CharacterType(CharacterTypeBase, table=True):
    parents: List["MyParentModel"] = Relationship(back_populates='chartype')

class CharacterTypeRead(CharacterTypeBase):
    id: int

# ---

class MyParentModelBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)


class MyParentModel(MyParentModelBase, table=True):
    chartype_id: Optional[int] = Field(
        default=None, foreign_key='charactertype.id',
    )
    chartype: Optional[CharacterType] = Relationship(back_populates="parents",
                                                     sa_relationship_kwargs={'lazy': 'subquery'})

class MyParentModelRead(MyParentModelBase):
    """Data Model returned to the user"""
    id: int
    chartype: str = Field(alias="type")

    @validator("chartype", pre=True)
    def get_char_type_name(cls, type_: CharacterTypeRead, values) -> str:
        # value['']
        return type_.name

    class Config:
        allow_population_by_field_name = True

And back in fastAPI

@app.get("/parents", response_model=List[MyParentModelRead])
def read_parents(offset: int = 0, limit: int = Query(default=100, lte=100)):
    with Session(engine) as session:
        parents = session.exec(select(MyParentModel).offset(offset).limit(limit)).all()
        return parents

This will correctly returns:

[
  {
    "id": 1,
    "name": "woot ",
    "type": "beast"
  }
]

Still, I think using validation to do this feels 'hackish' 🤷‍♂️

[LeonardoGentile]

You need to pass lazy='subquery' to sa_relationship_args/sa_relationship_kwargs, or just pass in the entire sa_relationship object

@lovetoburnswhen btw I couldn't find the sa_relationship_args/sa_relationship_kwargs in the SqlModel documentation.
Is there a common pattern to pass parameters to the underlying libs like sa_*_args / sa_*_kwargs to sqlAlchemy and similar for pydantic? Or is it something that ins't present in the documentation yet?

[YuriiMotov]

I think the most straightforward solution here would be to mimic structure of DB models in Read models: create the CompanyUserRead schema with the rank field and add it to UserRead schema.

class UserBase(SQLModel):
    username: str

class CompanyUserRead(SQLModel):
    rank: str

class UserRead(UserBase):
    company: CompanyUserRead

And then add a computed field to provide direct access to the rank field:

class UserRead(UserBase):
    company: CompanyUserRead

    @computed_field
    @property
    def rank(self) -> str:
        return self.company.rank

You can also exclude company field from output schema:

    company: CompanyUserRead = Field(exclude=True)

Runnable code example in the details:

Details

import uuid
from typing import List, Optional

from pydantic import computed_field
from sqlalchemy.orm import selectinload
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class UserBase(SQLModel):
    username: str


class User(UserBase, table=True):
    id: uuid.UUID = Field(primary_key=True)
    company: Optional["CompanyUser"] = Relationship(back_populates="user")


class CompanyUserRead(SQLModel):
    rank: str


class UserRead(UserBase):
    company: CompanyUserRead = Field(exclude=True)

    @computed_field
    @property
    def rank(self) -> str:
        return self.company.rank


class Company(SQLModel, table=True):
    id: uuid.UUID = Field(primary_key=True)
    name: str
    members: List["CompanyUser"] = Relationship(back_populates="company")


class CompanyUser(SQLModel, table=True):
    """
    Link Table to store ranks between users and a company
    """
    company_id: uuid.UUID = Field(foreign_key="company.id", primary_key=True)
    user_id: uuid.UUID = Field(foreign_key="user.id", primary_key=True)
    rank: str
    company: "Company" = Relationship(back_populates="members")
    user: "User" = Relationship(back_populates="company")

engine = create_engine("sqlite:///")
user_id = uuid.uuid4()

def init_db():
    SQLModel.metadata.create_all(engine)

    # Add data to DB
    with Session(engine) as session:
        company = Company(id=uuid.uuid4(), name="Company 1")
        user = User(id=user_id, username="user 1")
        company_user = CompanyUser(user=user, company=company, rank="123")
        session.add(company_user)
        session.commit()


def main():
    init_db()

    # Read User from DB
    with Session(engine) as session:
        user_db = session.get(User, user_id, options=[selectinload(User.company)])
    user_read = UserRead.model_validate(user_db)
    assert user_read.rank == "123"
    assert user_read.company.rank == "123"
    assert user_read.model_dump() == {"username": "user 1", "rank": "123"}


    # You can also validate UserRead from dict
    user_from_dict = UserRead.model_validate(
        {"username": "user 1", "company": {"rank": "123"}}
    )
    assert user_from_dict.rank == "123"
    assert user_from_dict.model_dump() == {"username": "user 1", "rank": "123"}

if __name__ == "__main__":
    main()

So, you will be able to access rank field as user.company.rank.
This is suitable if you don't have strict requirement to access rank on UserRead directly.

If you need to implement direct access from UserRead, then tricks with Pydantic validators seem to be the only way to do it.

As for treating this as feature request, I'm not sure this feature is so valuable - use case seems to be quite niche and there is already a way to implement this..
I would just convert this issue to discussion and mark the answer to make it easier to find for future readers.