In SQLAlchemy, for example, I'd like to search for keywords "foo", "bar" and possibly but not necessarily more keywords in the "description" column of my Item class, so I build a list of such keywords: ["foo", "bar"],
and then use primitive looping to achieve my goal:
query_obj = session.query(Item)
for k in search_keywords:
query_obj = query_obj.filter(Item.description.like('%{0}%'.format(k)))
but this doesn't seem very elegant. I think there could be a better solution in sqlalchemy. Could someone share some tips other than looping and chaining? Thanks.
p.s. Also what is the best practice to achieve a "OR" search of multiple(indefinite number of) keywords?
Your code is as elegant as it gets, I do not think you can improve it significantly.
In order to solve the same for OR, you can collect the clauses into a list first, and then apply or_ with the filter(...):
q = session.query(Item)
clauses = [Item.description.like('%{0}%'.format(k)) for k in search_keywords]
q = q.filter(or_(*clauses))
If you find it more elegant, you can use it for AND case you have as well by calling and_ instead of or_.