16

Hi SQLAlchemy experts out there, here's a tricky one for you:

I'm trying to write a query that resolves into something like:

SELECT * FROM MyTable where my_column LIKE ANY (array['a%', 'b%'])

using SQLAlchemy:

foo = ['a%', 'b%']

# this works, but is dirty and silly
DBSession().query(MyTable).filter("my_column LIKE ANY (array[" + ", ".join(["'" + f + "'" for f in token.tree_filters]) + "])")

# something like this should work (according to documentation), but doesn't (throws "AttributeError: Neither 'AnnotatedColumn' object nor 'Comparator' object has an attribute 'any'"
DBSession().query(MyTable).filter(MyTable.my_column.any(foo, operator=operators.like)

Any solutions?

Paul Lo
  • 6,032
  • 6
  • 31
  • 36
user1599438
  • 158
  • 1
  • 1
  • 5
  • Possible duplicate of [Elixir/SQLAlchemy equivalent to SQL "LIKE" statement?](http://stackoverflow.com/questions/3325467/elixir-sqlalchemy-equivalent-to-sql-like-statement) – dshgna Mar 16 '16 at 13:51

2 Answers2

26

Use or_() and like(), the following code should satisfy your need well:

from sqlalchemy import or_

foo = ['a%', 'b%']
DBSession().query(MyTable).filter(or_(*[MyTable.my_column.like(name) for name in foo]))

A where condition WHERE my_column LIKE 'a%' OR my_column LIKE 'b%' would be generated from above code.

As for why your any() didn't work, I think it's because it requires my_column to be a list (see here), and, for instance, query(MyTable).filter(MyTable.my_list_column.any(name='abc')) is to return MyTable rows if any element in my_list_column column (a list) of that row is named with 'abc', so it's actually quite different from your need.

Community
  • 1
  • 1
Paul Lo
  • 6,032
  • 6
  • 31
  • 36
  • Thanks for the reply. Using or_() was another solution that I thought of, but didn't want that because it can make the query quite long. But still better than my dirty solution, I guess. – user1599438 Feb 02 '15 at 19:58
  • @user1599438 I'm afraid you need to use `or_()` for this case, and it's actually not that long :p – Paul Lo Feb 03 '15 at 02:19
14

You can try to use any_()

In your case it would look something like this:

from sqlalchemy import any_

foo = ['a%', 'b%']
DBSession().query(MyTable).filter(MyTable.my_column.like(any_(foo)))
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
S. Blue
  • 149
  • 1
  • 3