mysql sqlalchemy keywords search

Question

I have array with keywords:

keywords = ["word1","word2","word3"]

and i have table of items:
items:
- id
- title
- date

i want find any record that has a title inside the title field
so i want to mix between like("%{word}%".format(word=word)) and "OR"
note that there is other fields so i cant simply make or between all of them i want this to be AND with the the other conditions but unable to figure out how to mark them as a group of OR but AND with the others

possible duplicate of [Using OR in SQLAlchemy](http://stackoverflow.com/questions/7942547/using-or-in-sqlalchemy) — Yaroslav Admin, Sep 08 '15 at 13:26

score 0 · Accepted Answer · answered Sep 07 '15 at 13:46

In my examples I assume your model is defined as below:

class Item(Base):
    id = Column(Integer, primary_key=True)
    title = Column(String)
    date = Column(Date)

To build OR condition for your keywords, you can use or_():

>>> keywords = ['word1', 'word2', 'word3']
>>> keywords_condition = or_(*[Item.title.like('%{}%'.format(keyword)) for keyword in keywords])
>>> keywords_condition
'item.title LIKE :title_1 OR item.title LIKE :title_2 OR item.title LIKE :title_3'

Then you can use filter() method to apply this condition + any amount of others. They will be joined with AND:

>>> Session.query(Item).filter(keywords_condition, Item.date == date.today())
'SELECT item.id AS item_id, item.title AS item_title, item.date AS item_date 
FROM item 
WHERE (item.title LIKE :title_1 OR item.title LIKE :title_2 OR item.title LIKE :title_3) AND item.date = :date_1'

mysql sqlalchemy keywords search

1 Answers1