I'm trying to implement the search in pset8 and want to find the most elegant and fast solution.
The way I have implemented it is to first of all check whether the search term is an int, and if so, search solely within the
postal_code field. If it is not an int, it searches within
place_name. Then, if there are less than 10 results, it does another search in
admin_name1 to check against states. This is my code:
@app.route("/search") def search(): """Search for places that match query.""" try: int(request.args.get("q")) postcode = True except: postcode = False q = request.args.get("q") + "%" if postcode: rows = db.execute("SELECT * FROM places WHERE postal_code LIKE :q LIMIT 10", q = q) else: rows = db.execute("SELECT * FROM places WHERE place_name LIKE :q LIMIT 10", q = q) if len(rows) < 10: newrows = db.execute("SELECT * FROM places WHERE admin_name1 LIKE :q LIMIT :limit", q = q, limit = 10 - len(rows)) rows = rows + newrows return jsonify(rows)
The reason I did not use an
OR clause is because I want the
place_name matches to come first, so that if you search for 'New York' for example, all the city matches come before the state matches as that's probably more relevant. So what I ideally want to do is to perform a single SELECT statement which returns a maximum of 10 results where the
place_name matches are returned first and the
admin_name1 matches come after that. Is this possible!?