Hi, I'm ThadeusB.

In many cases you might need a query based on multiple sets of information. Think of a search box, where you would like to provide the option to search in multiple areas (title, content, authors) or exclude these areas. Or a customer management system, in which a query can be provided by the customer name, phone number, or any other number of options that can be filtered down. There are of course, any number of situations that you may need IF logic in your database queries.

Appending results of several database SELECT statements would not be advisable, since it accesses the database several times, and leads to slow, inefficient design. The goal is to only hit the database once, while getting all of the needed information.This is where a dynamic query would come in handy. In web2py, this can be accomplished in multiple ways.

The simplest way of creating a dynamic web2py query is to start off with a blank Set object, and then filter the query down from there.

qset=db()
if arg1 == "xyz": qset=qset(db.abc.id > 0)
if arg2 == "xyz": qset=qset(db.def.id > 0)
qset.select()

For a more advanced version, you can append a query to a list and use python's reduce function.

queries=[]
if arg1 == "xyz": queries.append(db.abc.id > 0)
if arg2 == "xyz": queries.append(db.def.id > 0)
query = reduce(lambda a,b:(a&b),queries)
query.select()

Or if you are doing the same kind of query, to a list of results

q = request.vars.q
query = ( # Start off with a default query
              db.page.title.lower().like(q) 
            | db.page.content.lower().like(q)
)

# Then run through each element, and search for that specific word in the test.
for qs in filter(lambda a: a != '', q.split(' ')):
    query = query | db.page.content.lower().like(qs.strip())

rows = db(query).select()