CODEWITHSUNDEEP
pythonsqlalchemy
colorlace
colorlace

Reputation: 886

Filter on hybrid property evaluates to "where false"

I set up a mapping... 

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class Chap(Base):
    __tablename__ = 'chap'

    id = Column(Integer, primary_key=True)
    tophats = Column(Integer)
    exclamation = Column(String)

    @hybrid_property
    def hat_brag(self):
        return "I have "+str(self.tophats)+" tophats!"

    @hybrid_property
    def cold_complain(self):
        return self.exclamation+", it's cold!"

...and I created a chap.

>>>c1 = Chap(tophats=5, exclamation="Blimey")

Now I want to query this chap based on how he complains about the weather:

>>>print(Session().query(Chap).filter(Chap.cold_complain == "Blimey, it's cold!"))
SELECT chap.id AS chap_id, chap.tophats AS chap_tophats, chap.gloves AS chap_gloves, chap.exclamation AS chap_exclamation 
FROM chap 
WHERE chap.exclamation || :exclamation_1 = :param_1

That SQL code looks right, but now I want to query him on another hybrid_property, one that contains an int converted to a str...

>>>print(Session().query(Chap).filter(Chap.hat_brag == "I have 5 tophats!"))
SELECT chap.id AS chap_id, chap.tophats AS chap_tophats, chap.exclamation AS chap_exclamation 
FROM chap 
WHERE false

WHERE false. That doesn't look like the right query! Anyone know what's going on?

Upvotes: 3

Views: 4182

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121484

In a query, SQLAlchemy will use the getter method of a hybrid property without a dedicated @...expression() method to produce the necessary SQL objects used for a query. The getter method is bound to the class, not an instance, so self in this scenario will reference your Chap class.

For your cold_complain property, that means Chap.exclamation + ", it's cold!" is returned, resulting in a chap.exclamation || :exclamation_1 SQL expression; the + translated to the SQL concatenation operator.

But for your hat_brag property returns a string; the expression executed is really "I have "+str(Chap.tophats)+" tophats!", which becomes 'I have Chap.tophats tophats!':

>>> "I have "+str(Chap.tophats)+" tophats!"
'I have Chap.tophats tophats!'

That's a fixed, static string. Next, this is made part of your filter, with == "I have 5 tophats!". These two static string values are not equal:

>>> "I have "+str(Chap.tophats)+" tophats!" == "I have 5 tophats!"
False

So that's what is being used in the SQL query sent to the database.

What you want to do here instead is to use the expression() option and define a SQL-friendly version of your query:

from sqlalchemy.sql.expression import cast

class Chap(Base):
    # ...

    @hybrid_property
    def hat_brag(self):
        return "I have "+str(self.tophats)+" tophats!"

    @hat_brag.expression
    def hat_brag(cls):
        return "I have " + cast(cls.tophats, String) + " tophats!"

Now the expression method is used for queries, and the original function on instances, in Python:

>>> c1.hat_brag
'I have 5 tophats!'
>>> print(Session().query(Chap).filter(Chap.hat_brag == "I have 5 tophats!"))
SELECT chap.id AS chap_id, chap.tophats AS chap_tophats, chap.exclamation AS chap_exclamation
FROM chap
WHERE :param_1 || CAST(chap.tophats AS VARCHAR) || :param_2 = :param_3

Upvotes: 3

Related Questions