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.
2 Answers
Answers 1
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.
Answers 2
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 used only if completely necessary.
0 comments:
Post a Comment