Cannot use hybrid_property in filter_by

[Adrian]

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.

[Adrian]

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.

[Jean-Sébastien Suzanne]

Yes, I understand after, I answer too quickly

[Mike Bayer]

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.

[Adrian]

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

[Mike Bayer]

I don't really know what the solution would be. its best that the operator system doesn't try to guess intent.

[Mike Bayer]

if we want to say "x == y == z" should put parenthesis, that's something else to look into.

[Mike Bayer]

i dont really have a thought on this one right now

[Mike Bayer]

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.