CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Chandler Squires
Chandler Squires

Reputation: 407

Create an empty SQLAlchemy query as a base for searching a model

I want to search a model based on multiple criteria. The user enters a search term and selects which fields to search. If no fields are selected, I want to return an empty result. How do I create an empty query object? Or is there a better way to construct this?

results = *an empty query object*

if nameChecked:
    nameResults = System.query.filter(System.system_name.contains(searchTag))
    results = results.union(nameResults)

if descriptionChecked:
    descriptionResults = System.query.filter(System.system_description.contains(searchTag))
    results = results.union(descriptionResults)

if tagsChecked:
    tagsResults = System.query.filter(System.system_tags.contains(searchTag))
    results = results.union(tagsResults)

Upvotes: 3

Views: 2134

Answers (1)

davidism
davidism

Reputation: 127200

Rather than getting a separate checked value for each field, use multiple checkboxes with different values but the same name to get a list of fields to filter.

Check if no fields were selected and if so return an empty list. Otherwise, collect a list of filters and or_ them together. union is not necessary in this case.

<form>
    <input name="q">
    <input type="checkbox" name="field" value="name">
    <input type="checkbox" name="field" value="description">
    <input type="checkbox" name="field" value="tags">
    <input type="submit">
</form>

{% if results %}
    output the results
{% endif %}

@app.route('/search')
def search():
    value = request.args['q']
    fields = set(request.args.getlist('field'))

    if not fields:
        return render_template('search.html', results=[])

    filters = []

    if 'name' in fields:
        filters.append(System.name.contains(value))

    if 'description' in fields:
        filters.append(System.description.contains(value))

    if 'tags' in fields:
        filters.append(System.tags.contains(value))

    results = System.query.filter(db.or_(*filters)).all()
    return render_template('search.html', results=results)

You could get clever with a dictionary and compact the filters, but it's probably less efficient.

    results = System.query.filter(db.or_(*(value for key, value in {
        'name': System.name.contains(value),
        'description': System.description.contains(value),
        'tags': System.tags.contains(value)
    }.items() if key in fields))).all() if fields else []

If you really need an empty query as opposed to an empty result, you can use System.query.filter(False). This is of limited use because you can't do anything with the query afterwards, any filters will be ignored.

Upvotes: 0

Related Questions