How to do a general "search bar" search across multiple SQLAlchemy models

1
votes

I have a search bar for my app. Say the user searched for 'John Smith United Kingdom Oxford University' I would like to return the user from the accounts table with name 'John Smith', location 'United Kingdom' and education 'Oxford University'.

Here is a general overview for the search 'John Smith United Kingdom Oxford University':

  • Show accounts from the database with name 'John Smith', location 'United Kingdom' and education 'Oxford University'.
  • Show accounts with name 'John Smith' and education 'Oxford University'
  • Show accounts with name 'John Smith' and location 'United Kingdom'
  • Show accounts with name 'John Smith'

I cannot find any resources specifically for Flask-SQLAlchemy which specify how to do this. I'm aware I would have to use .like() though I don't know how to add it to a SQLAlchemy query in Flask. With SQLAlchemy I could do .where(column.like()) though I cannot in Flask-SQLAlchemy.

How would I go about doing this?

2
pythonmysqlflasksqlalchemyflask-sqlalchemy
So what about Smith College, the state of Georgia, or a person named Paul Ireland? How is your application supposed to know which tables to query with which pieces of the search string? Do you want to require the user to always use the exact format N N L L E E for name, location and education? - Lukas Graf
That's part of my question, as there is no set format for the search like you stated I would like to know how to implement it. For example Facebook and a search bar that recognises names, locations and education/work. When the user sets an account there is a fixed choice of locations which make things easier but name and education not fixed, so a user can include anything. - Pav Sidhu

2 Answers

1
votes

You use like() like you use it in SQLAlchemy:

User.query.filter(User.name.like("%John Smith%")).all()

or you can use contains():

User.query.filter(User.name.contains('John Smith')).all()
0
votes

Better you should use ilike

User.query.filter(User.name.ilike("%John%")).all()