0

I have few form fields on search page. After performing the search, my page should display a list of possible matching results. If the user typed in only part of a title, ISBN, or author name, search page should find matches for those as well. Also if user typed only one or few field - page should show all matches.

Idk how to write query. If i have one value from request.form and other values is None - so whole query is empty

@app.route('/search', methods=("GET", "POST"))
def search_book():
    books = None
    if request.method == "POST":
        isbn = request.form['isbn']
        title = request.form['title']
        author = request.form['author']
        year = request.form['year']
        books = db.query(Books).filter_by(isbn=isbn, title=title, author=author, year=year).all()
    return render_template("search.html", books=books)
Keplian
  • 25
  • 3

2 Answers2

1

.filter_by(multiple arguments) will default to AND, meaning the user will have to enter data that matches all fields

This also means that if the user leaves fields empty, the query will only return books that have (for example) title = " " (when title form is empty), despite having entered a valid year.

This is probably not intended, from a user point of view. A way to fix this is to (1. validate input data, and then) add filters to a list if they are not empty, then add the non-empty fields using or_(*filter_list). Query will then return all rows that match any field specified by the forms.

from sqlalchemy import or_
query = db.query(Books)
filter_list = []
if request.form['isbn']:
    filter_list.append(Book.isbn.ilike(request.form['isbn']))
if request.form['author']:
    filter_list.append(Book.author.ilike(request.form['author']))
# etc...

if len(filter_list) > 0:
    query = query.filter(or_(*filter_list))

didnt test code but *filter_list allows you to pass a list to the filter method, or_ allows you to change the default AND to OR

more here: Using OR in SQLAlchemy

c8999c 3f964f64
  • 1,430
  • 1
  • 12
  • 25
0

Use the 'or_()' method . This will search all the matching results for any given column

name = request.form.get('res')
result = Books.query.filter(db.or_(Books.author==name , Books.isbn==name , Books.title==name)).all()

'res' is the search entry given by the user in the search form in your html page , as mentioned it can be anything ISBN , title or the author's name

ManojKumar
  • 3
  • 3