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

Question

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?

dirn · Accepted Answer · 2014-08-22 20:45:49Z

6

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.

answered Aug 22, 2014 at 20:45

dirn

21.1k7 gold badges75 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chishaku Over a year ago

Amazing. Instead of LIKE, would a '==' query be more efficient? Tags are being selected from a pre-defined list so an '==' query would work in this case.

dirn Over a year ago

This is a large enough topic to warrant its own question.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related