[Fixed]-Maintain SQL operator precedence when constructing Q objects in Django

by

5👍

✅

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.

👤John Moutafis

5👍

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.

👤Kevin Christopher Henry

Leave a comment