Cannot use hybrid_property in filter_by

Issue #3308 wontfix
Adrian
created an issue
from sqlalchemy import *
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)
    foo = Column(Integer)

    @hybrid_property
    def foo_123(self):
        return self.foo == 123


e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.query(Test).filter_by(foo_123=True).all()

The WHERE clause generated from this is test.foo = 123 = true which fails. I think it should be WHERE (test.foo = 123) = true instead. Maybe a hybrid property could automatically insert parentheses around the generated criterion? That shouldn't break anything but avoid this bug.

Comments (8)

  1. Adrian reporter

    The 123 was just an example. The real code has this so a method is somewhat pointless:

    @hybrid_property
    def pending(self):
        return self.state == AgreementState.pending
    

    Also, it already works fine when using .filter() - it's just the filter_by() that's not working.

  2. Michael Bayer repo owner

    The WHERE clause generated from this is test.foo = 123 = true which fails.

    why does that fail? DB doesn't like the syntax ? that would be more an issue in the operator system. I'm not really sure why you'd want to output the SQL (test.foo = 123) = true in any case, I'd just use filter() here. I don't think SQLAlchemy can really get more into guessing intent here when one essentially says filter_by((x == 5) = True)

    Overall, if your hybrid_property already outputs a boolean expression, filter_by isn't appropriate. Just use filter:

    query(Test).filter(Test.foo_123).all()
    

    This has always worked for me. I don't see why filter_by() needs to support this.

  3. Adrian reporter

    Yes, the DB doesn't like that syntax.

    I don't mind using filter(). Just wanted to clarify if it's a bug (it feels like quite unexpected behavior, since it works fine for normal boolean columns).

  4. Michael Bayer repo owner

    I agree it's a little unfortunate that filter_by() doesn't have a way to guess on these things but ultimately it can be made to work on the user-end by providing an expression that handles the "eq(True)" use case. I'd rather not get into adding this directly to core since fundamantal changes like these have a habit of breaking other things.

  5. Log in to comment