Django query filter combining AND and OR with Q objects don't return the expected results

Question

I try to combine AND and OR in a filter using Q objects. It looks like that the | behave like an AND. This is related to the previous annotate which is run in the same query and not as a subquery.

What is the correct way to handle this with Django?

models.py

class Type(models.Model):     name = models.CharField(_('name'), max_length=100)     stock = models.BooleanField(_('in stock'), default=True)     hide = models.BooleanField(_('hide'), default=False)     deleted = models.BooleanField(_('deleted'), default=False)  class Item(models.Model):     barcode = models.CharField(_('barcode'), max_length=100, blank=True)     quantity = models.IntegerField(_('quantity'), default=1)     type = models.ForeignKey('Type', related_name='items', verbose_name=_('type')) 

views.py

def hire(request):     categories_list = Category.objects.all().order_by('sorting')     types_list = Type.objects.annotate(quantity=Sum('items__quantity')).filter(         Q(hide=False) & Q(deleted=False),         Q(stock=False) | Q(quantity__gte=1))     return render_to_response('equipment/hire.html', {            'categories_list': categories_list,            'types_list': types_list,            }, context_instance=RequestContext(request)) 

resulting SQL query

SELECT "equipment_type"."id" [...] FROM "equipment_type" LEFT OUTER JOIN     "equipment_subcategory" ON ("equipment_type"."subcategory_id" =     "equipment_subcategory"."id") LEFT OUTER JOIN "equipment_item" ON     ("equipment_type"."id" = "equipment_item"."type_id") WHERE      ("equipment_type"."hide" = False AND "equipment_type"."deleted" = False )     AND ("equipment_type"."stock" = False )) GROUP BY "equipment_type"."id"     [...] HAVING SUM("equipment_item"."quantity") >= 1 

expected SQL query

SELECT     * FROM     equipment_type LEFT JOIN (     SELECT type_id, SUM(quantity) AS qty     FROM equipment_item     GROUP BY type_id ) T1 ON id = T1.type_id WHERE hide=0 AND deleted=0 AND (T1.qty > 0 OR stock=0) 

---

EDIT: I added the expected SQL query (without the join on equipment_subcategory)

Answer 1

Try adding parentheses to explicitly specify your grouping? As you already figured out, multiple params to filter() are just joined via AND in the underlying SQL.

Originally you had this for the filter:

[...].filter(     Q(hide=False) & Q(deleted=False),     Q(stock=False) | Q(quantity__gte=1)) 

If you wanted (A & B) & (C | D) then this should work:

[...].filter(     Q(hide=False) & Q(deleted=False) &     (Q(stock=False) | Q(quantity__gte=1)))