Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can not apply filter on @hybrid_property in sqlalchemy

Tags:

sqlalchemy

The code is simple as:

Class A(Base):
    status = Columns(Integer)

    @hybrid_property
    def is_ok(self):
        return self.status > 0

But

session.query(A).filter(A.is_ok).all()

will raise Exception: TypeError: Boolean value of this clause is not defined.

I've tried print result[0].is_ok, type(result[0].is_ok). It was True <type 'bool'>.

So how can I filter based on a hybrid_property


My Answer

With the help from @van, I kind of knowing how.

The arg filter(*criterion) takes should be a SQL expression object. I used to think that it takes a boolean as arg, BUT NO!

The criterion is any SQL expression object applicable to the WHERE clause of a select. String expressions are coerced into SQL expression constructs via the text() construct.

Code tells:

# wrong code:
@is_ok.expression
def is_ok(self):
    return self.status > 30 and self.status < 90

# correct code:
from sqlalchemy.sql import and_
@is_ok.expression
def is_ok(self):
    return and_(self.status > 30, self.status < 90).label('is_ok')
like image 854
hbrls Avatar asked Oct 20 '22 09:10

hbrls


1 Answers

Add a custom expression. Below uses CASE statement, but you could use IF as well if you db supports it:

@is_ok.expression
def is_ok(cls):
    return case([(cls.status > 0, True)], else_=False).label("is_ok")

See Defining Expression Behavior Distinct from Attribute Behavior documentation for more info.

like image 186
van Avatar answered Dec 10 '22 05:12

van