2

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?

chishaku
  • 4,577
  • 3
  • 25
  • 33

1 Answers1

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.

dirn
  • 19,454
  • 5
  • 69
  • 74
  • 1
    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. – chishaku Aug 22 '14 at 20:51
  • 1
    This is a large enough topic to warrant [its own question](http://stackoverflow.com/questions/6142235/sql-like-vs-performance). – dirn Aug 22 '14 at 20:53