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?

Possible duplicate of Elixir/SQLAlchemy equivalent to SQL "LIKE" statement? — dshgna
– dshgna, Commented Mar 16, 2016 at 13:51

Community · Accepted Answer · 2017-05-23 11:46:36Z

27

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.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Feb 2, 2015 at 3:36

Paul Lo

6,1466 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1599438 Over a year ago

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.

Paul Lo Over a year ago

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

Ilja Everilä · Accepted Answer · 2018-06-06 10:46:45Z

17

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

edited Jun 6, 2018 at 10:46

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

answered Mar 3, 2017 at 7:59

S. Blue

1791 silver badge3 bronze badges

1 Comment

Duncan Over a year ago

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]?

Collectives™ on Stack Overflow

SQLAlchemy filter query "column LIKE ANY (array)"

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related