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.

Q() object is OK. It works the same way as evaluating a normal boolean expression with AND and OR. If you write a whole expression, it is parsed by Python with the correct precedence, but you use low-level operations step by step, it expects that you must execute them in the right order with creating appropriate temporary variables for subexpressions manually. Your code with `q.add(..., Q.operator)` is the same as `if item['operator'] == 'and': q = q & Q(...)` `elif item['operator'] == 'or': q = q | Q(...)` that is evaluated similarly to `boolean_variable |= boolean_value` immediately. — hynekcer, Jul 26 '17 at 18:19

Kevin Christopher Henry · Answer 1 · 2017-07-25T08:41:12.513

Since SQL precedence is the same as Python precedence when it comes to AND, OR, and NOT, you should be able to achieve what you want by letting Python parse the expression.

One quick-and-dirty way to do it would be to construct the expression as a string and let Python eval() it.

from functools import reduce

ops = ["&" if item["operator"] == "and" else "|" for item in expressions]
qs = [Q(**{item["field"]: item["value"]}) for item in expressions]

q_string = reduce(
    lambda acc, index: acc + " {op} qs[{index}]".format(op=ops[index], index=index),
    range(len(expressions)),
    "Q()"
) # equals "Q() | qs[0] | qs[1] & qs[2] | qs[3]"

q_expression = eval(q_string)

Python will parse this expression according to its own operator precedence, and the resulting SQL clause will match your expectations:

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

Of course, using eval() with user-supplied strings would be a major security risk, so here I'm constructing the Q objects separately (in the same way you did) and just referring to them in the eval string. So I don't think there are any additional security implications of using eval() here.

wow, very clever thinking outside of the box... love this workaround. I'll wait just to see if there is a way to make the ORM do this, otherwise i'll give it the bounty. Thanks — Martin Taleski, Jul 23 '17 at 20:27

John Moutafis · Accepted Answer · 2017-08-01T07:41:50.433

~~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.

The link to "jehiah.cz" above is not related because it is a workaround for a bug in Django that has been fixed many years ago (probably an early implementation of Q object). The problem in the question is not a problem in Q object, but an invalid order of evaluating the result. — hynekcer, Jul 26 '17 at 17:46

Maintain SQL operator precedence when constructing Q objects in Django

2 Answers2