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

by

8👍

✅

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) 
    ')
👤cgaspoz

60👍

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

15👍

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

8👍

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)
👤Rami Alloush

Leave a comment