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

angel-langdon commented 2 years ago

First Check

[X] I added a very descriptive title to this issue.
[X] I used the GitHub search to find a similar issue and didn't find it.
[X] I searched the SQLModel documentation, with the integrated search.
[X] I already searched in Google "How to X in SQLModel" and didn't find any information.
[X] I already read and followed all the tutorial in the docs and didn't find an answer.
[X] I already checked if it is not related to SQLModel but to Pydantic.
[X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

[X] 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

byrman commented 2 years ago

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()
)

stuartaccent commented 2 years ago

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
}

strobelo commented 2 years ago

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 absolutely saved me today. Thank you so much!

northtree commented 2 years ago

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

deZakelijke commented 2 years ago

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.

strobelo commented 2 years ago

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.

nagraj98 commented 1 year ago

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 ?

antont commented 1 year ago

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

samidarko commented 5 months ago

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
            )

50Bytes-dev commented 4 months ago

Use my PR #801

Kusmeroglu commented 3 months ago

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()
)

tiangolo / sqlmodel