CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
chishaku
chishaku

Reputation: 4643

Flask-SQLAlchemy 'OR' query with variable number of parameters

I have an advanced search interface that sends key:value pairs to my flask application. The key is the column in the table to search and the value is the condition to compare against.

Below is a sample query for projects where k is the 'tags' column in the Projects table and v is the tag to search against.

projects = Projects.query.filter(getattr(Projects, k).like('%{}%'.format(v)))

If the user submits two tags to search against, v will be a list of the two tags. I can then query the database like this:

if len(v) == 2:
    v1, v2 = v[0], v[1]
    projects = Projects.query.filter(or_(
        getattr(Projects, k).like('%{}%'.format(v1)),
        getattr(Projects, k).like('%{}%'.format(v2)))
    )

Is there a better way to perform the 'OR' query where the number of tags (in this example) is variable?

How can I write the code so it can handle 2 tags or 10 tags?

Upvotes: 2

Views: 3122

Answers (1)

dirn
dirn

Reputation: 20769

You can use * expansion to pass an arbitrary number of arguments to or_.

or_filters = [getattr(Projects, k).like('%{}%'.format(term)) for term in v]
projects = Projects.query.filter(or_(*or_filters))

Keep in mind that using a large number of LIKE lookups in a SQL query is usually inefficient.

Upvotes: 6

Related Questions