python - SQLAlchemy: How can I create a hybrid property for the disjunction of columns? -

i have user class stores "first_name" column , "alias" column describe person's name , nickname. want able query class find users either first_name or alias, wrote class this:

from sqlalchemy import column, integer, string, create_engine, orm sqlalchemy.ext.declarative import declarative_base sqlalchemy.ext.hybrid import hybrid_property   engine = create_engine('sqlite:///:memory:') base = declarative_base() session = orm.sessionmaker(engine)   class user(base):     __tablename__ = 'user'     id = column(integer, primary_key=true)     first_name = column(string(64))     alias = column(string(64), nullable=true)      @hybrid_property     def name(self):         return self.alias or self.first_name      def __repr__(self):         return 'user("{}" a.k.a. "{}")'.format(self.first_name, self.alias)   if __name__ == "__main__":     base.metadata.create_all(engine) 

this works. i'm able search users first name. i'm not able search users aliases.

>>> session = session() >>> user = user(first_name="edward", alias="ed") >>> session.add(user) >>> session.flush() >>> session.query(user).filter_by(name="edward").first() user("edward" a.k.a. "ed") >>> session.query(user).filter_by(name="ed").first() >>> 

this makes sense-- expressions generated name not ones want.

>>> print session.query(user).filter_by(name="ed") select "user".id user_id, "user".first_name user_first_name, "user".alias user_alias "user" "user".first_name = :first_name_1 

the sqlalchemy docs on hybrid properties seems suggest solution use hybrid_property.expression() change generated expression. however, can't see expression make sense return.

my first idea return sort of or:

from sqlalchemy.sql import or_  @name.expression def name(cls):     return or_(cls.first_name, cls.alias) 

but expressions generates predictably wrong:

select "user".id user_id, "user".first_name user_first_name, "user".alias user_alias "user" ("user".first_name or "user".alias) = :param_1 

how can make work?

after more studying , experimentation, turns out solution described in "building custom comparators" section. can use comparator object implement custom comparisons:

class either(comparator):     def __init__(self, left, right):         self.left = left         self.right = right      def __eq__(self, other):         return or_(other == self.left, other == self.right)   class user(base):     # ...      @name.comparator     def name(self):         return either(self.first_name, self.alias) 

which works expected:

>>> user user("edward" a.k.a. "ed") >>> print session.query(user).filter_by(name="edward") select "user".id user_id, "user".first_name user_first_name, "user".alias user_alias "user" "user".first_name = :first_name_1 or "user".alias = :alias_1 >>> print session.query(user).filter_by(name="edward").first() user("edward" a.k.a. "ed") >>> print session.query(user).filter_by(name="ed").first() user("edward" a.k.a. "ed") 

i think there more room improvement here studying "hybrid value objects", however, it's still evading me.