Login

Dynamic query builder with AND/OR

Author:
bobwaycott
Posted:
August 25, 2009
Language:
Python
Version:
1.1
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. Template tag - list punctuation for a list of items by shapiromatron 3 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 3 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 10 months, 3 weeks ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 11 months, 1 week ago
  5. Help text hyperlinks by sa2812 1 year ago

Comments

Please login first before commenting.