Login

Dynamic query builder with AND/OR

Author:
bobwaycott
Posted:
August 25, 2009
Language:
Python
Version:
1.1
Tags:
dynamic-query-builder and-or-query-builder
Score:
1 (after 1 ratings)

I recently had a need to build an iTunes style filtered search -- a user can add/subtract any number of filters whereby the are offered selects for fields they want to search, what type of search they wish to perform on that field (i.e., equals, contains, etc.), and then enter a value to search. Finally, they are provided the option to search all or any of the created filters.

To keep things simple, I created this dynamic query builder. It takes a Model, plus lists of fields, types, values, and the chosen operator (and/or). Then, it constructs actual Q objects for each, performing a small sanity check to ensure a blank value has not been passed in.

In the end, it returns either a filtered QuerySet or an empty result set to ensure that we can at least provide a message back to the user if nothing comes of trying to build the query.

One would use it like so:

results = dynamic_query(ModelName, fields_list, types_list, values_list, operator)
if results:
    # do something
else:
    # do something else
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
def dynamic_query(model, fields, types, values, operator):
    """
     Takes arguments & constructs Qs for filter()
     We make sure we don't construct empty filters that would
        return too many results
     We return an empty dict if we have no filters so we can
        still return an empty response from the view
    """
    from django.db.models import Q
    
    queries = []
    for (f, t, v) in zip(fields, types, values):
        # We only want to build a Q with a value
        if v != "":
            kwargs = {str('%s__%s' % (f,t)) : str('%s' % v)}
            queries.append(Q(**kwargs))
    
    # Make sure we have a list of filters
    if len(queries) > 0:
        q = Q()
        # AND/OR awareness
        for query in queries:
            if operator == "and":
                q = q & query
            elif operator == "or":
                q = q | query
            else:
                q = None
        if q:
            # We have a Q object, return the QuerySet
            return model.objects.filter(q)
    else:
        # Return an empty result
        return {}

More like this

  1. JSON-compatible query filter specification by mhalle 6 years, 11 months ago
  2. Create new variables in templates by jmrbcu 7 years, 1 month ago
  3. superSearch function for generating large OR queries by Neuman 5 years, 6 months ago
  4. FieldAccessForm (per-field user access for forms derived from models) by Killarny 6 years, 4 months ago
  5. Filter on Multiple M2M Objects Simultaneously by axiak 7 years, 11 months ago

Comments

Please login first before commenting.