title: dynamically connecting Django Q objects with AND and OR
body: <p>I want users to be able to query my database via several different parameters (name, year, etc), dynamically add more fields, and join them with boolean operators; the net result would be something like "year = 1900 AND name = chicken AND location = San Francisco." I think I'm doing something wrong, since it's not returning anything, even when I try just one field with a value that I know matches some data (e.g., I can get objects back when I use .filter() from the Django shell). Anyone know how I can fix it? </p>
  
  <p>Relevant view (ignore the sloppy indentation, I didn't want to go through and fix all of it, but it's right in my actual code):</p>
  
  <pre><code>class BaseSearchFormSet(BaseFormSet):
  def clean(self):
      if any(self.errors):
          return self.errors
      queries = []
      valid_courses = ["appetizer","main","dessert"]
      valid_period = re.compile(r'\d\d\d0-\d\d\d5|\d\d\d5-\d\d\d0')
      valid_year = re.compile(r'\d{4}')
      multi_rows = ["year","period","course"]
      for x in xrange(0,self.total_form_count()):
          form = self.forms[x]
          query = form.cleaned_data.get("query")
          row = form.cleaned_data.get("row")
          if query in queries and row not in multi_rows:
              raise forms.ValidationError("You're already searching for %s.")
          queries.append(query)
          if row == "course" and query.lower() not in valid_courses:
              raise forms.ValidationError("%s is not a valid course option."%(form.cleaned_data["query"]))
          if row == "period" and not re.match(valid_period,query):
              raise forms.ValidationError("%s is not a properly formatted period. Valid five-year periods span either the first or second half of a decade. For example: 1910-1915, 1925-1930."%(form.cleaned_data["query"]))
          if row == "year" and not re.match(valid_year,query):
              raise forms.ValidationError("Please enter a four-digit year.")
  def search(request):
  errors = []
  searchFormSet = formset_factory(F.SearchForm, extra=1,formset=BaseSearchFormSet)
  if request.GET:
      formset = searchFormSet(request.GET)
      forms = []
      if formset.is_valid():
          for x in xrange(0,formset.total_form_count()):
              form = {}
              form["row"]= formset[x].cleaned_data.get("row",None)
              form["query"] = formset[x].cleaned_data.get("query",None)
              form["bools"] = formset[x].cleaned_data.get("bools",None)
              if form["query"]:
                  q = form["query"]
              else:
                  errors.append("no query found")
              if form["row"]:
                  row = form["row"]
              else:
                  errors.append("no row found")
              filter_keys = {"dish_name":Q(dish__name__icontains=q),
                  "regex":Q(dish__full_name__regex=r'%s'%(q)),
                  "course":Q(dish__classification=q.lower()),
                  "year":Q(page__menu_id__year__exact=q),
                  "period":Q(page__menu_id__period__exact=q),
                  "location":Q(page__menu_id__location__icontains=q),
                  "restaurant":Q(page__menu_id__restaurant__icontains=q)}
              forms.append(form)
          final_query=Q()
          def var_reduce(op,slice):
              if op == "and":
                  return reduce(lambda x,y: x &amp; y,slice)
              elif op == "or":
                  return reduce(lambda x,y: x | y,slice)
          for x in xrange(len(forms)):
              try:
                  try:
                      if final_query:
                          slice = [final_query,filter_keys[forms[x]["row"]],filter_keys[forms[x+1]["row"]]]
                      else:
                          slice = [filter_keys[forms[x]["row"]],filter_keys[forms[x+1]["row"]]]
                      final_query = var_reduce(forms[x]["bools"],slice)
                  except IndexError:
                      if final_query:
                          slice = [final_query,filter_keys[forms[x]["row"]]]
                      else:
                          slice = [filter_keys[forms[x]["row"]]]
                      final_query = var_reduce(forms[x]["bools"],slice)
                  items = MenuItem.objects.filter(final_query)
                  return render_to_response("search_results.html",{"items":items,"formset":formset})
              except KeyError as e:
                  errors.append(e)    
                  formset = searchFormSet(None)
                  return render_to_response("search_page.html",{"errors":errors,"formset":formset})
      else:   
          formset = searchFormSet(None)
          return render_to_response("search_page.html",{"errors":errors,"formset":formset})
  else:
      formset = searchFormSet(None)
      return render_to_response("search_page.html",{"formset":formset})
  </code></pre>
  
  <p>models:</p>
  
  <pre><code>from django.db import models
  
  class MenuItem(models.Model):
  def format_price(self):
      return "${0:0&lt;4,.2f}".format(float(self.price))
  def __unicode__(self):
      return self.dish
  dish=models.OneToOneField('Dish',to_field='mk')
  price=models.CharField(max_length=5,blank=True)
  page=models.OneToOneField('MenuPage')
  mk=models.CharField(max_length=10,unique=True)
  formatted_price = property(format_price)
  class Menu(models.Model):
  def period(self):#adapted from http://stackoverflow.com/questions/2272149/round-to-5or-other-number-in-python
      try:
          p=int(10*round(float(int(self.year))/10))
          if p &lt; self.year:
              return "%s-%s"%(p,p+5)
          else:
              return "%s-%s"%(p-5,p)
      except (ValueError,TypeError):
          return ""
  def __unicode__(self):
      if self.restaurant:
          return self.restaurant
      else:
          return self.mk
  restaurant=models.TextField(unique=False,blank=True,null=True)
  year=models.CharField(max_length=4,unique=False,blank=True,null=True)
  location=models.TextField(unique=False,blank=True,null=True)
  status=models.CharField(unique=False,max_length=20)
  mk=models.CharField(max_length=8,unique=True,primary_key=True)
  period=property(period) 
  language = models.CharField(unique=False,max_length=30)
  #objects=MenuManager()
  
  class MenuPage(models.Model):
  mk=models.CharField(max_length=10,unique=True)
  menu_id=models.OneToOneField("Menu",to_field='mk')
  #objects=MenuPageManager()
  class Dish(models.Model):
  def __unicode__(self):
      return self.name
  full_name = models.TextField()
  name=models.CharField(unique=True,max_length=255)
  mk=models.CharField(max_length=10,unique=True)
  class Classification(models.Model):
  def __unicode__(self):
      if self.classification:
          return self.classification
      else:
          return "none"
  dish=models.OneToOneField('dish',to_field='name')
  classification=models.CharField(unique=False,max_length=9)
  mk=models.CharField(max_length=10,primary_key=True)
  </code></pre>
  
  <p>html of my search page:</p>
  
  <pre><code>{% extends "base.html" %}
  {% block style %}
  &lt;link rel="stylesheet" type="text/css" href="/static/search_style.css" /&gt;
  {% endblock %}
  {% block java %}
  &lt;script type="text/javascript" src="/static/searches.js"&gt;&lt;/script&gt;
  {% endblock %}
  {% block title %}Search{% endblock %}
  {% block head %}Search{% endblock %}
  {% block content %}
  {% autoescape off %}
  &lt;div id="searches"&gt;
  &lt;form id="search" action="" method="get"&gt;
      &lt;table border="0" cellpadding="0" cellspace="0"&gt;
          &lt;tbody class="search"&gt;
              {% for form in formset.forms %}
              &lt;tr&gt;
                  &lt;td class="row"&gt;{{ form.row }}&lt;/td&gt;
                  &lt;td class="query"&gt;{{ form.query }}&lt;/td&gt;
                  &lt;td class="bool"&gt;{{ form.bools }}&lt;/td&gt;
              &lt;/tr&gt;
          {% endfor %}
          &lt;/tbody&gt;
      &lt;/table&gt;
      {{ formset.management_form }}
      &lt;input type="submit" value="Submit" id="submit"&gt;
  &lt;/form&gt;
  &lt;/div&gt;
  {% if formset.errors or errors %}
  &lt;div id="errors"&gt;
  &lt;h3&gt;The following errors were encountered while trying to submit your search:&lt;/h3&gt;
  {% for x,y in formset.errors.items %}
  &lt;p&gt;{{ x }} : {{ y }}&lt;/p&gt;
  {% endfor %}
  {{ errors }}
  &lt;/div&gt;
  {% endif %}
  &lt;div id="notes"&gt;
  &lt;p&gt;Searching by dish names, locations, and restaurants is case-insensitive.&lt;/p&gt;
  &lt;p&gt;Searching by course uses  case-insensitive exact matching. Valid courses are Appetizer, Main, and Dessert.&lt;/p&gt;
  &lt;p&gt;Years should be entered YYYY. Five-year periods span either the first or second half of a decade, and should be entered YYYY-YYYY. Example valid five-year periods are 1900-1905, 1995-2000, etc.&lt;/p&gt;
  &lt;p&gt;Regular expression search follows MySQL regular expression syntax, as described &lt;a href="http://dev.mysql.com/doc/refman/5.1/en/regexp.html" target="_blank"&gt;here&lt;/a&gt;.&lt;/p&gt;
   &lt;/div&gt;
  
  {% endautoescape %}
  &lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;br /&gt; &lt;br /&gt;&lt;br /&gt;&lt;br /&gt;
  {% endblock %}
  
  {% block footer %}
  &lt;div id="warning"&gt;
  &lt;p&gt;NOTE: This site and the information it contains are still in development. Some information may be missing or inaccurate.&lt;/p&gt;
  &lt;/div&gt;
  &lt;div class="credits"&gt;
      Created and maintained by &lt;a href="/about#sam"&gt;Sam Raker&lt;/a&gt; and &lt;a href="/about#rachel"&gt;Rachel Rakov&lt;/a&gt;
      &lt;br /&gt;
      Data graciously provided by &lt;a href="http://menus.nypl.org" target="_blank"&gt;What's on the Menu?&lt;/a&gt;
  &lt;/div&gt;
  {% endblock %}
  </code></pre>