SQLAlchemy filter query "column LIKE ANY (array)"

Question

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?

Tarton

Possible duplicate of Elixir/SQLAlchemy equivalent to SQL "LIKE" statement? – Backbend

Answer 1

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.

Alkalimeter

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. – Tarton

@Tarton I'm afraid you need to use or_() for this case, and it's actually not that long :p – Alkalimeter

Answer 2

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

Johnny

How to use like any with JSON field like {"a": ["1", "5"]} if I want to find any item from an array [1, 7, 9]? – Dissatisfaction