django / query expression / negate
Suppose you have an is_enabled boolean in your
Django model.
class Rule(models.Model):
is_enabled = models.BooleanField(blank=True)
# other exciting fields here
And now imagine you want to negate the is_enabled values. Something
you would easily do in SQL, with:
UPDATE myapp_rule SET is_enabled = NOT is_enabled;
The Django F-syntax is nice, and looks like it should be up for the task.
Let’s sum up a couple of attempts:
Rule.objects.update(is_enabled=(not F('is_enabled')))
No! You get this:
UPDATE myapp_rule SET is_enabled = true;
Rule.objects.update(is_enabled=(True ^ F('is_enabled')))
No! You get this:
unsupported operand type(s) for ^: 'bool' and 'F'
And, as you might guess is_enabled=-F('is_enabled') and
is_enabled=~F('is_enabled') yield an error similar to the previous
one.
If you’re using MySQL, you just might get away with
is_enabled=(True - F('is_enabled')). But PostgreSQL will throw an
operator does not exist: boolean - boolean error.
Okay. So, on to implementing the not-operator for the ExpressionNode.
No! There is no __not__() operator for object instances. But we can
use __invert__() which is called by the ~-operator (bitwise not).
Getting that to work with Django 1.2, is a matter of this:
--- django/db/models/expressions.py.orig 2011-06-08 17:28:54.647385267 +0200
+++ django/db/models/expressions.py 2011-06-08 21:12:47.607603534 +0200
@@ -20,6 +20,9 @@ class ExpressionNode(tree.Node):
AND = '&'
OR = '|'
+ # Logical operators
+ NOT = 'NOT' # unary, needs special attention in combine_expression
+
def __init__(self, children=None, connector=None, negated=False):
if children is not None and len(children) > 1 and connector is None:
raise TypeError('You have to specify a connector.')
@@ -48,6 +51,10 @@ class ExpressionNode(tree.Node):
# OPERATORS #
#############
+ def __invert__(self):
+ obj = ExpressionNode([self], connector=self.NOT, negated=True)
+ return obj
+
def __add__(self, other):
return self._combine(other, self.ADD, False)
--- django/db/backends/__init__.py.orig 2011-06-08 20:59:19.307387242 +0200
+++ django/db/backends/__init__.py 2011-06-08 21:03:19.367604113 +0200
@@ -472,6 +472,9 @@ class BaseDatabaseOperations(object):
can vary between backends (e.g., Oracle with %% and &) and between
subexpression types (e.g., date expressions)
"""
+ if connector == 'NOT':
+ assert len(sub_expressions) == 1
+ return 'NOT (%s)' % sub_expressions[0]
conn = ' %s ' % connector
return conn.join(sub_expressions)
And now this works like it’s supposed to:
Rule.objects.update(is_enabled=~F('is_enabled'))
On to find out what the Django community thinks the real solution should be ;-)