Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Using hybrid_property of SQLAlchemy leads to CompileError #299

Open
8 tasks done
deZakelijke opened this issue Apr 14, 2022 · 11 comments
Open
8 tasks done

Using hybrid_property of SQLAlchemy leads to CompileError #299

deZakelijke opened this issue Apr 14, 2022 · 11 comments
Labels
investigate question Further information is requested

Comments

@deZakelijke
Copy link

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 sqlalchemy.ext.hybrid import hybrid_property  
from typing import Optional  
from sqlmodel import SQLModel, Field, create_engine  
  
from datetime import datetime  
                                    
                                      
class ObjectTable(SQLModel, table=True):  
    object_id: Optional[int] = Field(primary_key=True, default=None)  
    first_detection_time: datetime = Field(index=True)  
    last_detection_time: datetime = Field(index=True)  
      
    @hybrid_property                                    
    def detection_time(self) -> float:  
        return (self.last_detection_time - self.first_detection_time).total_seconds()  
      
    class Config:                 
        arbitrary_types_allowed = True  
                                                                                       
     
if __name__ == "__main__":  
    engine = create_engine("sqlite:///database.db")  
    SQLModel.metadata.create_all(engine)

Description

I am trying to create a hybrid property in an SQLModel class to allow more complex querying. Following the steps as described in the sqlalchemy docs here: https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html I assumed that this would work and create a valid table. However, this code gives the error:
sqlalchemy.exc.CompileError: (in table 'objecttable', column detection_time'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?
At first, I assumed that a type hint was missing so I added the float return type to the hybrid_property. I am not sure what the problem is exactly but I assumed that this code would yield a valid table.

Operating System

Linux

Operating System Details

Ubuntu 20.04

SQLModel Version

0.0.6

Python Version

Python 3.8.10

Additional Context

No response

@deZakelijke deZakelijke added the question Further information is requested label Apr 14, 2022
@alexbojko
Copy link

I faced with the same issue

@lhs950204
Copy link

me too. solved @alexbojko ...? please...

@alexbojko
Copy link

Nope. @tiangolo that would be really cool if you can take a look into it.

@kozickikarol
Copy link

kozickikarol commented Sep 17, 2022

Any update regarding this issue?

My model:

class Model(SQLModel: table=True):
    active_until: Optional[datetime.datetime]
    
    class Config:
        arbitrary_types_allowed = True
        
    @hybrid_property
    def is_active(self) -> bool:
        return self.active_until is not None and self.active_until > datetime.datetime.now()

On 0.0.8 I've got an error:

... python3.10/site-packages/sqlmodel/main.py", line 414, in get_sqlachemy_type
raise ValueError(f"The field {field.name} has no matching SQLAlchemy type")
ValueError: The field is_active has no matching SQLAlchemy type

@Augustinio
Copy link

Same issue over here @kozickikarol, any workarounds?

@antont
Copy link

antont commented Oct 20, 2022

I also struggled with column_property and hybrid_property. Am now using workaround where just call custom code to add properties in code to my "read" objects.

Am also using normal Python properties where suitable, actually, wouldn't that is_active work as a plain @property?

@Elyasomer
Copy link

@tiangolo Bro is there any problem in pull request if it be merged it would really help :)

@jjjacksn
Copy link

I found a somewhat simple workaround: annotate the property as a class var (which pydantic will ignore)

from typing import ClassVar, Optional  

from datetime import datetime  

from sqlalchemy.ext.hybrid import hybrid_property  
from sqlmodel import SQLModel, Field, create_engine  
                                      

def _detection_time(self) -> float:
    return (self.last_detection_time - self.first_detection_time).total_seconds()  

class ObjectTable(SQLModel, table=True):  
    object_id: Optional[int] = Field(primary_key=True, default=None)  
    first_detection_time: datetime = Field(index=True)  
    last_detection_time: datetime = Field(index=True)  
      
    detection_time: ClassVar[float] = hybrid_property(_detection_time)
     
if __name__ == "__main__":  
    engine = create_engine("sqlite:///database.db")  
    SQLModel.metadata.create_all(engine)

NOTE: I think in this example you need to add an expression for this to work as expected in queries.

@geudrik
Copy link

geudrik commented Jan 24, 2024

@tiangolo any update on this yet? The above feels like a work-around (and I'm so far not able to hybrid props and comparators working using sqlmodel and fast api)

@christianholland
Copy link

@jjjacksn Could you please elaborate how to add an expression to use the detection_time in a query?

For instance:

select(ObjectTable).where(ObjectTable.detection_time > 1000)

@50Bytes-dev
Copy link

@geudrik use my PR #801

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
investigate question Further information is requested
Projects
None yet
Development

No branches or pull requests