Is it possible to use a function in an SQLAlchemy filter?

Question

I have a function that checks an object for some properties and returns boolean values depending on the result. It's too complex to write it in filter, but it works and returns the right value.

Now I want to use sqlalchemy to return all objects that this function returns True for. I tried:

DBSession.query(MyObject).filter(self.check_attributes(MyObject) == True).all()

and

DBSession.query(MyObject).filter(self.check_attributes(MyObject)).all()

Both failed to select the right objects. What am I doing wrong?

Answer 1

As I said in my comment, hybrid_method/hybrid_property is the appropriate pattern for your use case. It may at first seem complicated, but it's actually quite simple. The first version of the function operates exactly like a Python method or property, and the second part acts as a class method. SQLAlchemy filters work on the class to generate SQL.

This is just a useless example, but instead of therabouts your example could have a complicated calculation.

If you don't need to pass any parameters then I suggest using hybrid_property instead.

class MyObject(Model):
  name = Column(String)
  num = Column(Integer)

  @hybrid_method
  def therabouts(self, n):
     return self.num > n - 5 and self.num <= n + 5

  @therabouts.expression
  def therabouts(cls, n):
     return and_(cls.num > n - 5, cls.num <= n + 5)

  @hybrid_property
  def is_al(self):
     return self.name.lower().startswith('al')

  @is_al.expression
  def is_al(cls):
     return cls.name.ilike('al%')

# When used as a class method @thereabouts.expression is called
near20 = session.query(MyObject).filter(MyObject.therabouts(20)).first()

# When used as an instance, @hybrid_method is called
near20.therabouts(20) # True
near20.therabouts(22) # Maybe True
near20.therabouts(50) # False

# filter example (class)
all_als = session.query(MyObject).filter(MyObject.is_al).all()
for al in all_als:
  print al.name
# output Alan, Alanzo, Albert...

# instance example (self)
bob = MyObject(name='Robert')
print bob.is_al # False

Answer 2

If by "use a function" you mean write the filters inside a function, you certainly can - you just need to pass the correct argument to it (the query) and use its return (the filtered query) in the proper place

Let's take an example from SQLAlchemy's tutorial:

wendy = session.query(User).filter_by(name='wendy').one() 

You can move the filter(s) to a function easily:

def my_filter(query):
    return query.filter_by(name='wendy')

wendy = my_filter(session.query(User)).one() 

---

However, if by " function that checks an object for some properties and returns boolean values depending on the result" you mean a function that accepts a database record as an argument, I don't think it can be done (without subverting the whole purpose of using SQL).

--EDIT-- As doog abides points out, the hybrid extension, while not operating on database records, does something that's equivalent for many practical purposes.

---

Of course, some people will insist in writing something like:

all_users = session.query(User).all()
wendy= filter( lambda u:u.name=='wendy', all_users )

But, for the sake of the sanity of your fellow programmers and users, please don't do that.