Open clstaudt opened 1 year ago
You need to use mapping (https://docs.sqlalchemy.org/en/14/orm/inheritance.html) to achieve this, but it's a bit tricky with sqlmodel.
Example of working code:
from typing import Optional, List
from sqlmodel import SQLModel, Field, Relationship, create_engine, Session
from sqlalchemy.orm import registry, with_polymorphic
mapper_registry = registry()
class Contract(SQLModel, table=True):
"""A contract defines the business conditions of a project"""
id: Optional[int] = Field(default=None, primary_key=True)
title: str = Field(description="Short description of the contract.")
client: "Client" = Relationship(
back_populates="contracts",
)
client_id: Optional[int] = Field(
default=None,
foreign_key="client.id",
)
currency: str
term_of_payment: Optional[int] = Field(
description="How many days after receipt of invoice this invoice is due.",
default=31,
)
contact_type: str
__mapper_args__ = {
"polymorphic_identity": "contract",
"polymorphic_on": "contact_type",
}
@mapper_registry.mapped
class TimeContract(Contract, table=True):
"""A time-based contract with a rate per time unit"""
contract_id: Optional[int] = Field(
default=None, foreign_key="contract.id", primary_key=True
)
rate: float = Field(
description="Rate of remuneration",
)
unit: str = Field(
description="Unit of time tracked. The rate applies to this unit.",
default="hour",
)
__mapper_args__ = {
"polymorphic_identity": "time",
}
@mapper_registry.mapped
class WorksContract(Contract, table=True):
"""A contract with a fixed price"""
contract_id: Optional[int] = Field(
default=None, foreign_key="contract.id", primary_key=True
)
price: float = Field(
description="Price of the contract",
)
deliverable: str = Field(description="Description of the deliverable")
__mapper_args__ = {
"polymorphic_identity": "works",
}
class Client(SQLModel, table=True):
"""A client the freelancer has contracted with."""
id: Optional[int] = Field(default=None, primary_key=True)
name: str
contracts: List["Contract"] = Relationship(back_populates="client")
engine = create_engine(
"sqlite:///",
# echo=True,
)
with Session(engine) as session:
SQLModel.metadata.create_all(engine)
c = Client()
c.name = "client name"
c.contracts = [
TimeContract(currency=12.2, title="title1", term_of_payment=1, rate=3.4),
WorksContract(
title="title2",
currency=43.4,
term_of_payment=6,
price=344.2,
deliverable="---",
),
TimeContract(currency=13.2, title="title3", term_of_payment=12, rate=56.4),
]
session.add(c)
session.commit()
session.refresh(c)
contract_manager = with_polymorphic(Contract, [TimeContract, WorksContract])
query = session.query(contract_manager)
for i in query.all():
print(i)
Edit:
Maybe we can fix the problem mentioned in 2 with this:
(main.py:292)
for k, v in new_cls.__fields__.items():
if isinstance(getattr(new_cls, k, None), InstrumentedAttribute):
continue
col = get_column_from_field(v)
Thanks @meirdev for the code example, I will try that.
I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.
If I do not care about minimizing the size of the database, is there still a problem with this?
Would it be too much to ask for a framework like SQLModel to abstract away these parts of the code so that it's straightforward to use inheritance in models? Perhaps some black Python magic could add this in the background when subclasses are created.
__mapper_args__ = {
"polymorphic_identity": "time",
}
contract_manager = with_polymorphic(Contract, [TimeContract, WorksContract])
@mapper_registry.mapped
From a user perspective, querying by Contract
base class rather than contract_manager
is more intuitive. Could this be a direction for an enhancement
? @tiangolo
If I do not care about minimizing the size of the database, is there still a problem with this?
I think this is ok, and it looks like sqlalchemy updates both tables each time.
t = session.get(TimeContract, 1)
t.title = "new title"
session.add(t)
session.commit()
INFO sqlalchemy.engine.Engine UPDATE contract SET title=? WHERE contract.id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00010s] ('new title', 1)
INFO sqlalchemy.engine.Engine UPDATE timecontract SET title=? WHERE timecontract.id = ? AND timecontract.contract_id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00005s] ('new title', 1, 1)
All these configurations give you flexibility to manipulate and query your data, for example: if you want to know how much contract time you have, you don't need to use with_polymorphic
:
session.query(TimeContract.id).count()
2. I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.
SQLModel thinks the fields of the parent table are in the child table. This currently makes it unusable to use with joined table inheritance.
This workaround proposed here with mapper_registry = registry()
does not work for me.
Ideally, joined table inheritance should work out of the box in SQLModel. In the meantime, does anyone have a fully working example?
Hello, Is there anything new on this topic since then? The proposed code fails with sqlmodel 0.0.16 (using pydantic 2.6.4 and sqlalchemy 2.0.28). Is there another approach that is more SQLmodel-oriented? Fred
Same issue here
Inheritance support would be really nice
Is this really an inheritance issue? Looks more like an import issue.
I'm having the same issue, but when I merge all models in one module, then problem is gone.
The problem is I got many models with lots of code, so it's better to keep them separate. But to fix the circular import issue, I used the proposed workaround to use TYPE_CHEKING
from the documents.
It fixes the circular import error, but seems like is causing this problem.
I would like to suggest an enhancement for SQLModel to support single table inheritance, similar to what is available in SQLAlchemy. This feature would allow us to define a class hierarchy on a single database table, which can be extremely useful for scenarios where different types of entities share common attributes but also have their unique fields. It would greatly enhance the flexibility and power of SQLModel. Here's the SQLAlchemy documentation for reference: Single Table Inheritance.
First Check
Commit to Help
Example Code
Description
I would like to understand inheritance better using the following example:
The code example is my first attempt at implementing this. However, It is not yet correct:
Questions:
Contract
is related to a client. That makes me thinkclient
should be a member of theContract
base class. However, I don't understand how to properly implement the relationship. Is it necessary to moveclient
to the table classes (thereby duplicating it)?select
fromContract
(rather than from the different contract types separately)?Operating System
macOS, Other
Operating System Details
No response
SQLModel Version
0.0.8
Python Version
3.10
Additional Context
No response