How to build a query dynamically, based on conditions?

Question

I want to query the datastore on a range (0-4) of possible criteria, based on a user request. Queries in NDB are built as follows:

query = Account.query(Account.userid >= 40, Account.userid < 50)

Is there any way I could do something like:

myfilter = []
myfilter.push('Account.userid >= 40')
myfilter.push('Account.userid < 50')
myfilter.push('Account.name == "John"')
query = Account.query(*myfilter)

There may be anywhere from 0 to 4 filter arguments, depending on conditionals. My assumption (which could be wrong) is that it is more optimal to omit a filter than have a catch-all (ex. Account.userid == *) if there is no need for it.

I know you can chain filters but since query objects are immutable, not sure this helps me.

Answer 1

Yes, it is possible. From Filtering by Property Values:

Instead of specifying an entire query filter in a single expression, you may find it more convenient to build it up in steps: for example:

query1 = Account.query()  # Retrieve all Account entitites
query2 = query1.filter(Account.userid >= 40)  # Filter on userid >= 40
query3 = query2.filter(Account.userid < 50)  # Filter on userid < 50 too

query3 is equivalent to the query variable from the previous example. Note that query objects are immutable, so the construction of query2 does not affect query1 and the construction of query3 does not affect query1 or query2.

You can use such incremental query build technique to conditionally add filters as needed. For example (this assumes an overall AND between the optional conditions):

query = Account.query()  # Retrieve all Account entitites
loggin.error(query)

if filter_by_userid:
    query = query.filter(Account.userid >= 40, Account.userid < 50)
    loggin.error(query)

if filter_by_username:
    query = query.filter(Account.name == "John")
    loggin.error(query)

loggin.error('Final: %s' % query)

The above snippet specifically takes advantage of the imutability of the query object, each assignment to the query variable actually storing the new query object obtained by applying the respective filter. Confirmed by the associated log messages.

Answer 2

There is a more elegant way of doing this. And btw is more dynamic:

def build_query_by(ndb_class, filters, sorts):
    """
    ndb_class: the ndb model class to query
    filters: a list of tuples of properties, operations and values
    sorts: a list of tuples of properties and order symbol
    """
    q = ndb_class.query()
    for prop, operation, value in filters:
        if operation == '==':
            q = q.filter(getattr(ndb_class, prop) == value)
        elif operation == '>=':
            q = q.filter(getattr(ndb_class, prop) >= value)
        elif operation == '<=':
            q = q.filter(getattr(ndb_class, prop) <= value)
        # more operations...

    for prop, symbol in sorts:
        if symbol == '-':
            q = q.order(-getattr(ndb_class, prop))
        else:
            q = q.order(getattr(ndb_class, prop))
    return q