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

56,560

Solution 1

OK, no success here or on #django. So I choose to use a raw SQL query to solve this problem...

Here the working code:

types_list = Type.objects.raw('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) 
    ')

Solution 2

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)))

Solution 3

This answer is late but could be helpful to a lot of people out there.

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

This will generate something similar to

WHERE (hide=0 AND deleted=0 AND (T1.qty > 0 OR stock=0))

Solution 4

Here's my example of complicated query, I hope you find it helpful

or_condition = Q()
and_condition = Q(company=request.user.profile.company)

for field in MyModel._meta.get_fields():
    if field.name != 'created_on' and field.name != 'company':
        or_condition.add(
            Q(**{"{}__icontains".format(field.name): query}), Q.OR)

and_condition.add(or_condition2, Q.AND)
MyModel.objects.filter(and_condition)

The problem with this method is that you get an empty (AND: ) case in your or_condition. It doesn't affect the query at all, but it annoys me! My current solution is as follows

import operator
from functools import reduce

and_condition = Q(company=request.user.profile.company)
or_condition = reduce(operator.or_, (Q(**{"{}__icontains".format(field.name): query})
                                     for field in MyModel._meta.get_fields() if field.name != 'created_on' and field.name != 'company'))

and_condition.add(or_condition, Q.AND)
MyModel.objects.filter(and_condition)
Share:
56,560
cgaspoz
Author by

cgaspoz

Updated on July 09, 2022

Comments

  • cgaspoz
    cgaspoz almost 2 years

    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)

  • cgaspoz
    cgaspoz over 13 years
    I also tried this solution, but it still don't handle the request correctly. Given the documentation at docs.djangoproject.com/en/dev/topics/db/queries/…, filter(Q&Q).filter(Q|Q) or filter(Q&Q, Q|Q) or filter(Q&Q&(Q|Q)) should all behave the same way. And I my case, it's the wrong way...
  • istruble
    istruble over 13 years
    Have you tried it with something that is not added via annotate()? The documentation for the AND and OR logic for filter() and exclude() is not bullet proof so keep inspecting the actual queries. The documentation shows the OR'd clauses in the SQL but I do not see that with 1.2.3 and sqlite3. I do see it when I do Qa & Qb & (Qc | Qd).
  • Andy Clifton
    Andy Clifton almost 3 years
    .. and gals, too.