Calculated columns not working (sqlaquemy.orm.column_property)

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 List

from sqlalchemy import func
from sqlalchemy import select as sa_select
from sqlalchemy.orm import column_property
from sqlmodel import create_engine, Field, Relationship, SQLModel


class Hero(SQLModel, table=True):
    name: str = Field(primary_key=True)
    team_name: str = Field(foreign_key="team.name")
    team: "Team" = Relationship(back_populates="heroes")


class Team(SQLModel, table=True):
    name: str = Field(primary_key=True)
    heroes_count = column_property(
        sa_select(func.count(Hero.team_name))
        .where(Hero.team_name == name)
        .correlate_except(Hero)
        .scalar_subquery()
    )
    heroes: List["Hero"] = Relationship(back_populates="team")


if __name__ == "__main__":
    engine = create_engine("sqlite:///test.db")
    SQLModel.metadata.create_all(engine)

Description

Create a Hero model
Crate a Team model
Create a calculated column using sqlalquemy.orm.column_property (https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#using-column-property)
Create all tables and it will raise the sqlalquemy.exc.InvalidRequestError

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Hero->hero, expression 'Team' failed to locate a name ('Team').
If this is a class name, consider adding this relationship() to the <class '__main__.Hero'> class after both dependent classes have been defined.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

Python 3.9.6

Additional Context

No response

Is assigning the property after the class definition instead of inline a workaround?

Team.heroes_count = column_property(
    sa_select(func.count(Hero.name))
    .where(Hero.team_name == Team.name)
    .correlate_except(Hero)
    .scalar_subquery()
)

As a workaround. Am pretty new to this lib so sorry if its not very good of a solution. Dont know of the impacts this has.

from datetime import datetime

from sqlalchemy import TIMESTAMP, func, cast
from sqlalchemy.orm import column_property, declared_attr
from sqlmodel import DateTime, Field, Column

class MyModel(SQLModel, table=True):
    start: datetime = Field(...)
    end: datetime = Field(...)

    @declared_attr
    def difference(self):
        return column_property(
            func.extract(
                'epoch',
                cast(self.end, TIMESTAMP) -
                cast(self.start, TIMESTAMP)
            )
        )

This will work for an internal value that doesnt go out in the api response.

Then you can define a response_model like so and just return the instance:

class MyModelOut(MyModel):
    prop_difference: Decimal = Field(alias="difference")

Response

{
  "start": "2022-03-11T16:14:42.413349",
  "end": "2022-03-11T16:19:06.093423",
  "difference": 263.680074
}

@angel-langdon did you manage to have column_property from select?

As a workaround. Am pretty new to this lib so sorry if its not very good of a solution. Dont know of the impacts this has.

from datetime import datetime

from sqlalchemy import TIMESTAMP, func, cast
from sqlalchemy.orm import column_property, declared_attr
from sqlmodel import DateTime, Field, Column

class MyModel(SQLModel, table=True):
    start: datetime = Field(...)
    end: datetime = Field(...)

    @declared_attr
    def difference(self):
        return column_property(
            func.extract(
                'epoch',
                cast(self.end, TIMESTAMP) -
                cast(self.start, TIMESTAMP)
            )
        )

This will work for an internal value that doesnt go out in the api response.

Then you can define a response_model like so and just return the instance:

class MyModelOut(MyModel):
    prop_difference: Decimal = Field(alias="difference")

Response

{
  "start": "2022-03-11T16:14:42.413349",
  "end": "2022-03-11T16:19:06.093423",
  "difference": 263.680074
}

This looks exactly like what I need but when I tried this I got the error Cannot compile Column object until its 'name' is assigned.

This looks exactly like what I need but when I tried this I got the error Cannot compile Column object until its 'name' is assigned.

I was able to resolve this by explicitly specifying the sa_column attribute in any model's fields that the column_property references:

class MyModel(SQLModel, table=True)
  phone_number: int = Field(nullable=False)

gives Cannot compile Column object until its 'name' is assigned., whereas

class MyModel(SQLModel, table=True)
  phone_number: int = Field(sa_column=sa.Column(sa.Integer(), nullable=False))

works.

As a workaround. Am pretty new to this lib so sorry if its not very good of a solution. Dont know of the impacts this has.

from datetime import datetime

from sqlalchemy import TIMESTAMP, func, cast
from sqlalchemy.orm import column_property, declared_attr
from sqlmodel import DateTime, Field, Column

class MyModel(SQLModel, table=True):
    start: datetime = Field(...)
    end: datetime = Field(...)

    @declared_attr
    def difference(self):
        return column_property(
            func.extract(
                'epoch',
                cast(self.end, TIMESTAMP) -
                cast(self.start, TIMESTAMP)
            )
        )

This will work for an internal value that doesnt go out in the api response.

Then you can define a response_model like so and just return the instance:

class MyModelOut(MyModel):
    prop_difference: Decimal = Field(alias="difference")

Response

{
  "start": "2022-03-11T16:14:42.413349",
  "end": "2022-03-11T16:19:06.093423",
  "difference": 263.680074
}

I was so happy to see this solution and even happier when it didn't throw any errors ! But my happiness was shortlived...

Below is my class (its rather simple!) but the order_number column is NOT getting created in my table.

from typing import Optional
from sqlmodel import Field, SQLModel
from sqlalchemy import func, literal, cast, String

class Order(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    @declared_attr
    def order_number(self):
        return column_property(
            # tried this commented one first, but it too didn't create the column
            # literal("ORD-") + cast(self.id, String)
            func.concat('ORD-', self.id)
        )

Am I missing something peeps ?

the order_number column is NOT getting created in my table.

That's the whole idea of column_property, to have something appear in the objects, which is not in the db, but computed from values in the db. Like in SQLAlchemy's example:

class User(Base):
    __tablename__ = "user"

    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))
    
    fullname = column_property(firstname + " " + lastname)

https://docs.sqlalchemy.org/en/20/orm/mapped_sql_expr.html#using-column-property

This worked for me

    @computed_field(return_type=str)
    @declared_attr
    def hello_world(self):
        return column_property(
            func.concat('hello ', "world")
        )

Update, this is working:

    # from the Conversation table
    @computed_field(return_type=Optional[bool])
    @property
    def has_any_unread_message(self):
        if session := object_session(self):
            return (
                session.exec(
                    select(Message)
                    .where(Message.conversation_id == self.id)
                    .limit(1)
                ).first()
                is not None
            )

Use my PR #801

Another way to work around this:

class Team(SQLModel, table=True):
    name: str = Field(primary_key=True)
    heroes: List["Hero"] = Relationship(back_populates="team")
    
    @computed_field
    @property
    def heroes_count(self) -> int:
        return self._heroes_count

    
Team._heroes_count = column_property(
    sa_select(func.count(Hero.team_name))
    .where(Hero.team_name == name)
    .correlate_except(Hero)
    .scalar_subquery()
)

Navigation Menu

Repository navigation

Calculated columns not working (`sqlaquemy.orm.column_property`) #240

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Calculated columns not working (sqlaquemy.orm.column_property) #240

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

byrman commented on Feb 12, 2022

stuartaccent commented on Mar 12, 2022

strobelo commented on Mar 26, 2022

northtree commented on Apr 13, 2022

deZakelijke commented on Apr 14, 2022

strobelo commented on Jul 9, 2022

nagraj98 commented on Jun 7, 2023

antont commented on Jun 8, 2023

samidarko commented on Feb 3, 2024

50Bytes-dev commented on Mar 1, 2024

Kusmeroglu commented on Apr 24, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Calculated columns not working (`sqlaquemy.orm.column_property`) #240