Open yudjinn opened 3 years ago
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.
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
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.
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
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).
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.
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'
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.
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.
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?
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
@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' š¤·āāļø
You need to pass
lazy='subquery'
tosa_relationship_args
/sa_relationship_kwargs
, or just pass in the entiresa_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?
First Check
Commit to Help
Example Code
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/