Maintain SQL operator precedence when constructing Q objects in Django

Question

I am trying to construct a complex query in Django by adding Q objects based on a list of user inputs:

from django.db.models import Q

q = Q()

expressions = [
    {'operator': 'or', 'field': 'f1', 'value': 1},
    {'operator': 'or', 'field': 'f2', 'value': 2},
    {'operator': 'and', 'field': 'f3', 'value': 3},
    {'operator': 'or', 'field': 'f4', 'value': 4},
]

for item in expressions:
    if item['operator'] == 'and':
       q.add(Q(**{item['field']:item['value']}), Q.AND )

    elif item['operator'] == 'or':
       q.add(Q(**{item['field']:item['value']}), Q.OR )

Based on this I am expecting to get a query with the following where condition:

f1 = 1 or f2 = 2 and f3 = 3 or f4 = 4

which, based on the default operator precedence will be executed as

f1 = 1 or (f2 = 2 and f3 = 3) or f4 = 4

however, I am getting the following query:

((f1 = 1 or f2 = 2) and f3 = 3) or f4 = 4

It looks like the Q() object forces the conditions to be evaluated in the order they were added.

Is there a way that I can keep the default SQL precedence? Basically I want to tell the ORM not to add parenthesis in my conditions.

Answer 1

Seems that you are not the only one with a similar problem. (edited due to @hynekcer 's comment)

A workaround would be to "parse" the incoming parameters into a list of Q() objects and create your query from that list:

from operator import or_
from django.db.models import Q

query_list = []

for item in expressions:
    if item['operator'] == 'and' and query_list:
        # query_list must have at least one item for this to work
        query_list[-1] = query_list[-1] & Q(**{item['field']:item['value']})
    elif item['operator'] == 'or':
        query_list.append(Q(**{item['field']:item['value']}))
    else:
        # If you find yourself here, something went wrong...

Now the query_list contains the individual queries as Q() or the Q() AND Q() relationships between them.
The list can be reduce()d with the or_ operator to create the remaining OR relationships and used in a filter(), get() etc. query:

MyModel.objects.filter(reduce(or_, query_list))

---

PS: Although Kevin's answer is clever, using eval() is considered a bad practice and should be avoided.