7
.. currentmodule:: django.db.models
9
Django offers a wide variety of :ref:`built-in lookups <field-lookups>` for
10
filtering (for example, ``exact`` and ``icontains``). This documentation
11
explains how to write custom lookups and how to alter the working of existing
12
lookups. For the API references of lookups, see the :doc:`/ref/models/lookups`.
14
A simple lookup example
15
~~~~~~~~~~~~~~~~~~~~~~~
17
Let's start with a simple custom lookup. We will write a custom lookup ``ne``
18
which works opposite to ``exact``. ``Author.objects.filter(name__ne='Jack')``
19
will translate to the SQL::
21
"author"."name" <> 'Jack'
23
This SQL is backend independent, so we don't need to worry about different
26
There are two steps to making this work. Firstly we need to implement the
27
lookup, then we need to tell Django about it. The implementation is quite
30
from django.db.models import Lookup
32
class NotEqual(Lookup):
35
def as_sql(self, qn, connection):
36
lhs, lhs_params = self.process_lhs(qn, connection)
37
rhs, rhs_params = self.process_rhs(qn, connection)
38
params = lhs_params + rhs_params
39
return '%s <> %s' % (lhs, rhs), params
41
To register the ``NotEqual`` lookup we will just need to call
42
``register_lookup`` on the field class we want the lookup to be available. In
43
this case, the lookup makes sense on all ``Field`` subclasses, so we register
44
it with ``Field`` directly::
46
from django.db.models.fields import Field
47
Field.register_lookup(NotEqual)
49
We can now use ``foo__ne`` for any field ``foo``. You will need to ensure that
50
this registration happens before you try to create any querysets using it. You
51
could place the implementation in a ``models.py`` file, or register the lookup
52
in the ``ready()`` method of an ``AppConfig``.
54
Taking a closer look at the implementation, the first required attribute is
55
``lookup_name``. This allows the ORM to understand how to interpret ``name__ne``
56
and use ``NotEqual`` to generate the SQL. By convention, these names are always
57
lowercase strings containing only letters, but the only hard requirement is
58
that it must not contain the string ``__``.
60
We then need to define the ``as_sql`` method. This takes a ``SQLCompiler``
61
object, called ``qn``, and the active database connection. ``SQLCompiler``
62
objects are not documented, but the only thing we need to know about them is
63
that they have a ``compile()`` method which returns a tuple containing a SQL
64
string, and the parameters to be interpolated into that string. In most cases,
65
you don't need to use it directly and can pass it on to ``process_lhs()`` and
68
A ``Lookup`` works against two values, ``lhs`` and ``rhs``, standing for
69
left-hand side and right-hand side. The left-hand side is usually a field
70
reference, but it can be anything implementing the :ref:`query expression API
71
<query-expression>`. The right-hand is the value given by the user. In the
72
example ``Author.objects.filter(name__ne='Jack')``, the left-hand side is a
73
reference to the ``name`` field of the ``Author`` model, and ``'Jack'`` is the
76
We call ``process_lhs`` and ``process_rhs`` to convert them into the values we
77
need for SQL using the ``qn`` object described before. These methods return
78
tuples containing some SQL and the parameters to be interpolated into that SQL,
79
just as we need to return from our ``as_sql`` method. In the above example,
80
``process_lhs`` returns ``('"author"."name"', [])`` and ``process_rhs`` returns
81
``('"%s"', ['Jack'])``. In this example there were no parameters for the left
82
hand side, but this would depend on the object we have, so we still need to
83
include them in the parameters we return.
85
Finally we combine the parts into a SQL expression with ``<>``, and supply all
86
the parameters for the query. We then return a tuple containing the generated
87
SQL string and the parameters.
89
A simple transformer example
90
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
92
The custom lookup above is great, but in some cases you may want to be able to
93
chain lookups together. For example, let's suppose we are building an
94
application where we want to make use of the ``abs()`` operator.
95
We have an ``Experiment`` model which records a start value, end value, and the
96
change (start - end). We would like to find all experiments where the change
97
was equal to a certain amount (``Experiment.objects.filter(change__abs=27)``),
98
or where it did not exceed a certain amount
99
(``Experiment.objects.filter(change__abs__lt=27)``).
102
This example is somewhat contrived, but it nicely demonstrates the range of
103
functionality which is possible in a database backend independent manner,
104
and without duplicating functionality already in Django.
106
We will start by writing a ``AbsoluteValue`` transformer. This will use the SQL
107
function ``ABS()`` to transform the value before comparison::
109
from django.db.models import Transform
111
class AbsoluteValue(Transform):
114
def as_sql(self, qn, connection):
115
lhs, params = qn.compile(self.lhs)
116
return "ABS(%s)" % lhs, params
118
Next, lets register it for ``IntegerField``::
120
from django.db.models import IntegerField
121
IntegerField.register_lookup(AbsoluteValue)
123
We can now run the queries we had before.
124
``Experiment.objects.filter(change__abs=27)`` will generate the following SQL::
126
SELECT ... WHERE ABS("experiments"."change") = 27
128
By using ``Transform`` instead of ``Lookup`` it means we are able to chain
129
further lookups afterwards. So
130
``Experiment.objects.filter(change__abs__lt=27)`` will generate the following
133
SELECT ... WHERE ABS("experiments"."change") < 27
135
Subclasses of ``Transform`` usually only operate on the left-hand side of the
136
expression. Further lookups will work on the transformed value. Note that in
137
this case where there is no other lookup specified, Django interprets
138
``change__abs=27`` as ``change__abs__exact=27``.
140
When looking for which lookups are allowable after the ``Transform`` has been
141
applied, Django uses the ``output_field`` attribute. We didn't need to specify
142
this here as it didn't change, but supposing we were applying ``AbsoluteValue``
143
to some field which represents a more complex type (for example a point
144
relative to an origin, or a complex number) then we may have wanted to specify
145
``output_field = FloatField``, which will ensure that further lookups like
146
``abs__lte`` behave as they would for a ``FloatField``.
148
Writing an efficient abs__lt lookup
149
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
151
When using the above written ``abs`` lookup, the SQL produced will not use
152
indexes efficiently in some cases. In particular, when we use
153
``change__abs__lt=27``, this is equivalent to ``change__gt=-27`` AND
154
``change__lt=27``. (For the ``lte`` case we could use the SQL ``BETWEEN``).
156
So we would like ``Experiment.objects.filter(change__abs__lt=27)`` to generate
159
SELECT .. WHERE "experiments"."change" < 27 AND "experiments"."change" > -27
161
The implementation is::
163
from django.db.models import Lookup
165
class AbsoluteValueLessThan(Lookup):
168
def as_sql(self, qn, connection):
169
lhs, lhs_params = qn.compile(self.lhs.lhs)
170
rhs, rhs_params = self.process_rhs(qn, connection)
171
params = lhs_params + rhs_params + lhs_params + rhs_params
172
return '%s < %s AND %s > -%s' % (lhs, rhs, lhs, rhs), params
174
AbsoluteValue.register_lookup(AbsoluteValueLessThan)
176
There are a couple of notable things going on. First, ``AbsoluteValueLessThan``
177
isn't calling ``process_lhs()``. Instead it skips the transformation of the
178
``lhs`` done by ``AbsoluteValue`` and uses the original ``lhs``. That is, we
179
want to get ``27`` not ``ABS(27)``. Referring directly to ``self.lhs.lhs`` is
180
safe as ``AbsoluteValueLessThan`` can be accessed only from the
181
``AbsoluteValue`` lookup, that is the ``lhs`` is always an instance of
184
Notice also that as both sides are used multiple times in the query the params
185
need to contain ``lhs_params`` and ``rhs_params`` multiple times.
187
The final query does the inversion (``27`` to ``-27``) directly in the
188
database. The reason for doing this is that if the self.rhs is something else
189
than a plain integer value (for example an ``F()`` reference) we can't do the
190
transformations in Python.
193
In fact, most lookups with ``__abs`` could be implemented as range queries
194
like this, and on most database backends it is likely to be more sensible to
195
do so as you can make use of the indexes. However with PostgreSQL you may
196
want to add an index on ``abs(change)`` which would allow these queries to
199
Writing alternative implementations for existing lookups
200
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
202
Sometimes different database vendors require different SQL for the same
203
operation. For this example we will rewrite a custom implementation for
204
MySQL for the NotEqual operator. Instead of ``<>`` we will be using ``!=``
205
operator. (Note that in reality almost all databases support both, including
206
all the official databases supported by Django).
208
We can change the behavior on a specific backend by creating a subclass of
209
``NotEqual`` with a ``as_mysql`` method::
211
class MySQLNotEqual(NotEqual):
212
def as_mysql(self, qn, connection):
213
lhs, lhs_params = self.process_lhs(qn, connection)
214
rhs, rhs_params = self.process_rhs(qn, connection)
215
params = lhs_params + rhs_params
216
return '%s != %s' % (lhs, rhs), params
217
Field.register_lookup(MySQLNotExact)
219
We can then register it with ``Field``. It takes the place of the original
220
``NotEqual`` class as it has the same ``lookup_name``.
222
When compiling a query, Django first looks for ``as_%s % connection.vendor``
223
methods, and then falls back to ``as_sql``. The vendor names for the in-built
224
backends are ``sqlite``, ``postgresql``, ``oracle`` and ``mysql``.
226
How Django determines the lookups and transforms which are used
227
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
229
In some cases you may which to dynamically change which ``Transform`` or
230
``Lookup`` is returned based on the name passed in, rather than fixing it. As
231
an example, you could have a field which stores coordinates or an arbitrary
232
dimension, and wish to allow a syntax like ``.filter(coords__x7=4)`` to return
233
the objects where the 7th coordinate has value 4. In order to do this, you
234
would override ``get_lookup`` with something like::
236
class CoordinatesField(Field):
237
def get_lookup(self, lookup_name):
238
if lookup_name.startswith('x'):
240
dimension = int(lookup_name[1:])
244
return get_coordinate_lookup(dimension)
245
return super(CoordinatesField, self).get_lookup(lookup_name)
247
You would then define ``get_coordinate_lookup`` appropriately to return a
248
``Lookup`` subclass which handles the relevant value of ``dimension``.
250
There is a similarly named method called ``get_transform()``. ``get_lookup()``
251
should always return a ``Lookup`` subclass, and ``get_transform()`` a
252
``Transform`` subclass. It is important to remember that ``Transform``
253
objects can be further filtered on, and ``Lookup`` objects cannot.
255
When filtering, if there is only one lookup name remaining to be resolved, we
256
will look for a ``Lookup``. If there are multiple names, it will look for a
257
``Transform``. In the situation where there is only one name and a ``Lookup``
258
is not found, we look for a ``Transform`` and then the ``exact`` lookup on that
259
``Transform``. All call sequences always end with a ``Lookup``. To clarify:
261
- ``.filter(myfield__mylookup)`` will call ``myfield.get_lookup('mylookup')``.
262
- ``.filter(myfield__mytransform__mylookup)`` will call
263
``myfield.get_transform('mytransform')``, and then
264
``mytransform.get_lookup('mylookup')``.
265
- ``.filter(myfield__mytransform)`` will first call
266
``myfield.get_lookup('mytransform')``, which will fail, so it will fall back
267
to calling ``myfield.get_transform('mytransform')`` and then
268
``mytransform.get_lookup('exact')``.