| 1 |
=========================== |
| 2 |
How to write custom lookups |
| 3 |
=========================== |
| 4 |
|
| 5 |
.. currentmodule:: django.db.models |
| 6 |
|
| 7 |
Django offers a wide variety of :ref:`built-in lookups <field-lookups>` for |
| 8 |
filtering (for example, ``exact`` and ``icontains``). This documentation |
| 9 |
explains how to write custom lookups and how to alter the working of existing |
| 10 |
lookups. For the API references of lookups, see the :doc:`/ref/models/lookups`. |
| 11 |
|
| 12 |
A lookup example |
| 13 |
================ |
| 14 |
|
| 15 |
Let's start with a small custom lookup. We will write a custom lookup ``ne`` |
| 16 |
which works opposite to ``exact``. ``Author.objects.filter(name__ne='Jack')`` |
| 17 |
will translate to the SQL: |
| 18 |
|
| 19 |
.. code-block:: sql |
| 20 |
|
| 21 |
"author"."name" <> 'Jack' |
| 22 |
|
| 23 |
This SQL is backend independent, so we don't need to worry about different |
| 24 |
databases. |
| 25 |
|
| 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:: |
| 28 |
|
| 29 |
from django.db.models import Lookup |
| 30 |
|
| 31 |
|
| 32 |
class NotEqual(Lookup): |
| 33 |
lookup_name = "ne" |
| 34 |
|
| 35 |
def as_sql(self, compiler, connection): |
| 36 |
lhs, lhs_params = self.process_lhs(compiler, connection) |
| 37 |
rhs, rhs_params = self.process_rhs(compiler, connection) |
| 38 |
params = lhs_params + rhs_params |
| 39 |
return "%s <> %s" % (lhs, rhs), params |
| 40 |
|
| 41 |
To register the ``NotEqual`` lookup we will need to call ``register_lookup`` on |
| 42 |
the field class we want the lookup to be available for. In this case, the lookup |
| 43 |
makes sense on all ``Field`` subclasses, so we register it with ``Field`` |
| 44 |
directly:: |
| 45 |
|
| 46 |
from django.db.models import Field |
| 47 |
|
| 48 |
Field.register_lookup(NotEqual) |
| 49 |
|
| 50 |
Lookup registration can also be done using a decorator pattern:: |
| 51 |
|
| 52 |
from django.db.models import Field |
| 53 |
|
| 54 |
|
| 55 |
@Field.register_lookup |
| 56 |
class NotEqualLookup(Lookup): ... |
| 57 |
|
| 58 |
We can now use ``foo__ne`` for any field ``foo``. You will need to ensure that |
| 59 |
this registration happens before you try to create any querysets using it. You |
| 60 |
could place the implementation in a ``models.py`` file, or register the lookup |
| 61 |
in the ``ready()`` method of an ``AppConfig``. |
| 62 |
|
| 63 |
Taking a closer look at the implementation, the first required attribute is |
| 64 |
``lookup_name``. This allows the ORM to understand how to interpret ``name__ne`` |
| 65 |
and use ``NotEqual`` to generate the SQL. By convention, these names are always |
| 66 |
lowercase strings containing only letters, but the only hard requirement is |
| 67 |
that it must not contain the string ``__``. |
| 68 |
|
| 69 |
We then need to define the ``as_sql`` method. This takes a ``SQLCompiler`` |
| 70 |
object, called ``compiler``, and the active database connection. |
| 71 |
``SQLCompiler`` objects are not documented, but the only thing we need to know |
| 72 |
about them is that they have a ``compile()`` method which returns a tuple |
| 73 |
containing an SQL string, and the parameters to be interpolated into that |
| 74 |
string. In most cases, you don't need to use it directly and can pass it on to |
| 75 |
``process_lhs()`` and ``process_rhs()``. |
| 76 |
|
| 77 |
A ``Lookup`` works against two values, ``lhs`` and ``rhs``, standing for |
| 78 |
left-hand side and right-hand side. The left-hand side is usually a field |
| 79 |
reference, but it can be anything implementing the :ref:`query expression API |
| 80 |
<query-expression>`. The right-hand is the value given by the user. In the |
| 81 |
example ``Author.objects.filter(name__ne='Jack')``, the left-hand side is a |
| 82 |
reference to the ``name`` field of the ``Author`` model, and ``'Jack'`` is the |
| 83 |
right-hand side. |
| 84 |
|
| 85 |
We call ``process_lhs`` and ``process_rhs`` to convert them into the values we |
| 86 |
need for SQL using the ``compiler`` object described before. These methods |
| 87 |
return tuples containing some SQL and the parameters to be interpolated into |
| 88 |
that SQL, just as we need to return from our ``as_sql`` method. In the above |
| 89 |
example, ``process_lhs`` returns ``('"author"."name"', [])`` and |
| 90 |
``process_rhs`` returns ``('"%s"', ['Jack'])``. In this example there were no |
| 91 |
parameters for the left hand side, but this would depend on the object we have, |
| 92 |
so we still need to include them in the parameters we return. |
| 93 |
|
| 94 |
Finally we combine the parts into an SQL expression with ``<>``, and supply all |
| 95 |
the parameters for the query. We then return a tuple containing the generated |
| 96 |
SQL string and the parameters. |
| 97 |
|
| 98 |
A transformer example |
| 99 |
===================== |
| 100 |
|
| 101 |
The custom lookup above is great, but in some cases you may want to be able to |
| 102 |
chain lookups together. For example, let's suppose we are building an |
| 103 |
application where we want to make use of the ``abs()`` operator. |
| 104 |
We have an ``Experiment`` model which records a start value, end value, and the |
| 105 |
change (start - end). We would like to find all experiments where the change |
| 106 |
was equal to a certain amount (``Experiment.objects.filter(change__abs=27)``), |
| 107 |
or where it did not exceed a certain amount |
| 108 |
(``Experiment.objects.filter(change__abs__lt=27)``). |
| 109 |
|
| 110 |
.. note:: |
| 111 |
This example is somewhat contrived, but it nicely demonstrates the range of |
| 112 |
functionality which is possible in a database backend independent manner, |
| 113 |
and without duplicating functionality already in Django. |
| 114 |
|
| 115 |
We will start by writing an ``AbsoluteValue`` transformer. This will use the SQL |
| 116 |
function ``ABS()`` to transform the value before comparison:: |
| 117 |
|
| 118 |
from django.db.models import Transform |
| 119 |
|
| 120 |
|
| 121 |
class AbsoluteValue(Transform): |
| 122 |
lookup_name = "abs" |
| 123 |
function = "ABS" |
| 124 |
|
| 125 |
Next, let's register it for ``IntegerField``:: |
| 126 |
|
| 127 |
from django.db.models import IntegerField |
| 128 |
|
| 129 |
IntegerField.register_lookup(AbsoluteValue) |
| 130 |
|
| 131 |
We can now run the queries we had before. |
| 132 |
``Experiment.objects.filter(change__abs=27)`` will generate the following SQL: |
| 133 |
|
| 134 |
.. code-block:: sql |
| 135 |
|
| 136 |
SELECT ... WHERE ABS("experiments"."change") = 27 |
| 137 |
|
| 138 |
By using ``Transform`` instead of ``Lookup`` it means we are able to chain |
| 139 |
further lookups afterward. So |
| 140 |
``Experiment.objects.filter(change__abs__lt=27)`` will generate the following |
| 141 |
SQL: |
| 142 |
|
| 143 |
.. code-block:: sql |
| 144 |
|
| 145 |
SELECT ... WHERE ABS("experiments"."change") < 27 |
| 146 |
|
| 147 |
Note that in case there is no other lookup specified, Django interprets |
| 148 |
``change__abs=27`` as ``change__abs__exact=27``. |
| 149 |
|
| 150 |
This also allows the result to be used in ``ORDER BY`` and ``DISTINCT ON`` |
| 151 |
clauses. For example ``Experiment.objects.order_by('change__abs')`` generates: |
| 152 |
|
| 153 |
.. code-block:: sql |
| 154 |
|
| 155 |
SELECT ... ORDER BY ABS("experiments"."change") ASC |
| 156 |
|
| 157 |
And on databases that support distinct on fields (such as PostgreSQL), |
| 158 |
``Experiment.objects.distinct('change__abs')`` generates: |
| 159 |
|
| 160 |
.. code-block:: sql |
| 161 |
|
| 162 |
SELECT ... DISTINCT ON ABS("experiments"."change") |
| 163 |
|
| 164 |
When looking for which lookups are allowable after the ``Transform`` has been |
| 165 |
applied, Django uses the ``output_field`` attribute. We didn't need to specify |
| 166 |
this here as it didn't change, but supposing we were applying ``AbsoluteValue`` |
| 167 |
to some field which represents a more complex type (for example a point |
| 168 |
relative to an origin, or a complex number) then we may have wanted to specify |
| 169 |
that the transform returns a ``FloatField`` type for further lookups. This can |
| 170 |
be done by adding an ``output_field`` attribute to the transform:: |
| 171 |
|
| 172 |
from django.db.models import FloatField, Transform |
| 173 |
|
| 174 |
|
| 175 |
class AbsoluteValue(Transform): |
| 176 |
lookup_name = "abs" |
| 177 |
function = "ABS" |
| 178 |
|
| 179 |
@property |
| 180 |
def output_field(self): |
| 181 |
return FloatField() |
| 182 |
|
| 183 |
This ensures that further lookups like ``abs__lte`` behave as they would for |
| 184 |
a ``FloatField``. |
| 185 |
|
| 186 |
Writing an efficient ``abs__lt`` lookup |
| 187 |
======================================= |
| 188 |
|
| 189 |
When using the above written ``abs`` lookup, the SQL produced will not use |
| 190 |
indexes efficiently in some cases. In particular, when we use |
| 191 |
``change__abs__lt=27``, this is equivalent to ``change__gt=-27`` AND |
| 192 |
``change__lt=27``. (For the ``lte`` case we could use the SQL ``BETWEEN``). |
| 193 |
|
| 194 |
So we would like ``Experiment.objects.filter(change__abs__lt=27)`` to generate |
| 195 |
the following SQL: |
| 196 |
|
| 197 |
.. code-block:: sql |
| 198 |
|
| 199 |
SELECT .. WHERE "experiments"."change" < 27 AND "experiments"."change" > -27 |
| 200 |
|
| 201 |
The implementation is:: |
| 202 |
|
| 203 |
from django.db.models import Lookup |
| 204 |
|
| 205 |
|
| 206 |
class AbsoluteValueLessThan(Lookup): |
| 207 |
lookup_name = "lt" |
| 208 |
|
| 209 |
def as_sql(self, compiler, connection): |
| 210 |
lhs, lhs_params = compiler.compile(self.lhs.lhs) |
| 211 |
rhs, rhs_params = self.process_rhs(compiler, connection) |
| 212 |
params = lhs_params + rhs_params + lhs_params + rhs_params |
| 213 |
return "%s < %s AND %s > -%s" % (lhs, rhs, lhs, rhs), params |
| 214 |
|
| 215 |
|
| 216 |
AbsoluteValue.register_lookup(AbsoluteValueLessThan) |
| 217 |
|
| 218 |
There are a couple of notable things going on. First, ``AbsoluteValueLessThan`` |
| 219 |
isn't calling ``process_lhs()``. Instead it skips the transformation of the |
| 220 |
``lhs`` done by ``AbsoluteValue`` and uses the original ``lhs``. That is, we |
| 221 |
want to get ``"experiments"."change"`` not ``ABS("experiments"."change")``. |
| 222 |
Referring directly to ``self.lhs.lhs`` is safe as ``AbsoluteValueLessThan`` |
| 223 |
can be accessed only from the ``AbsoluteValue`` lookup, that is the ``lhs`` |
| 224 |
is always an instance of ``AbsoluteValue``. |
| 225 |
|
| 226 |
Notice also that as both sides are used multiple times in the query the params |
| 227 |
need to contain ``lhs_params`` and ``rhs_params`` multiple times. |
| 228 |
|
| 229 |
The final query does the inversion (``27`` to ``-27``) directly in the |
| 230 |
database. The reason for doing this is that if the ``self.rhs`` is something else |
| 231 |
than a plain integer value (for example an ``F()`` reference) we can't do the |
| 232 |
transformations in Python. |
| 233 |
|
| 234 |
.. note:: |
| 235 |
In fact, most lookups with ``__abs`` could be implemented as range queries |
| 236 |
like this, and on most database backends it is likely to be more sensible to |
| 237 |
do so as you can make use of the indexes. However with PostgreSQL you may |
| 238 |
want to add an index on ``abs(change)`` which would allow these queries to |
| 239 |
be very efficient. |
| 240 |
|
| 241 |
A bilateral transformer example |
| 242 |
=============================== |
| 243 |
|
| 244 |
The ``AbsoluteValue`` example we discussed previously is a transformation which |
| 245 |
applies to the left-hand side of the lookup. There may be some cases where you |
| 246 |
want the transformation to be applied to both the left-hand side and the |
| 247 |
right-hand side. For instance, if you want to filter a queryset based on the |
| 248 |
equality of the left and right-hand side insensitively to some SQL function. |
| 249 |
|
| 250 |
Let's examine case-insensitive transformations here. This transformation isn't |
| 251 |
very useful in practice as Django already comes with a bunch of built-in |
| 252 |
case-insensitive lookups, but it will be a nice demonstration of bilateral |
| 253 |
transformations in a database-agnostic way. |
| 254 |
|
| 255 |
We define an ``UpperCase`` transformer which uses the SQL function ``UPPER()`` to |
| 256 |
transform the values before comparison. We define |
| 257 |
:attr:`bilateral = True <django.db.models.Transform.bilateral>` to indicate that |
| 258 |
this transformation should apply to both ``lhs`` and ``rhs``:: |
| 259 |
|
| 260 |
from django.db.models import Transform |
| 261 |
|
| 262 |
|
| 263 |
class UpperCase(Transform): |
| 264 |
lookup_name = "upper" |
| 265 |
function = "UPPER" |
| 266 |
bilateral = True |
| 267 |
|
| 268 |
Next, let's register it:: |
| 269 |
|
| 270 |
from django.db.models import CharField, TextField |
| 271 |
|
| 272 |
CharField.register_lookup(UpperCase) |
| 273 |
TextField.register_lookup(UpperCase) |
| 274 |
|
| 275 |
Now, the queryset ``Author.objects.filter(name__upper="doe")`` will generate a case |
| 276 |
insensitive query like this: |
| 277 |
|
| 278 |
.. code-block:: sql |
| 279 |
|
| 280 |
SELECT ... WHERE UPPER("author"."name") = UPPER('doe') |
| 281 |
|
| 282 |
Writing alternative implementations for existing lookups |
| 283 |
======================================================== |
| 284 |
|
| 285 |
Sometimes different database vendors require different SQL for the same |
| 286 |
operation. For this example we will rewrite a custom implementation for |
| 287 |
MySQL for the NotEqual operator. Instead of ``<>`` we will be using ``!=`` |
| 288 |
operator. (Note that in reality almost all databases support both, including |
| 289 |
all the official databases supported by Django). |
| 290 |
|
| 291 |
We can change the behavior on a specific backend by creating a subclass of |
| 292 |
``NotEqual`` with an ``as_mysql`` method:: |
| 293 |
|
| 294 |
class MySQLNotEqual(NotEqual): |
| 295 |
def as_mysql(self, compiler, connection, **extra_context): |
| 296 |
lhs, lhs_params = self.process_lhs(compiler, connection) |
| 297 |
rhs, rhs_params = self.process_rhs(compiler, connection) |
| 298 |
params = lhs_params + rhs_params |
| 299 |
return "%s != %s" % (lhs, rhs), params |
| 300 |
|
| 301 |
|
| 302 |
Field.register_lookup(MySQLNotEqual) |
| 303 |
|
| 304 |
We can then register it with ``Field``. It takes the place of the original |
| 305 |
``NotEqual`` class as it has the same ``lookup_name``. |
| 306 |
|
| 307 |
When compiling a query, Django first looks for ``as_%s % connection.vendor`` |
| 308 |
methods, and then falls back to ``as_sql``. The vendor names for the in-built |
| 309 |
backends are ``sqlite``, ``postgresql``, ``oracle`` and ``mysql``. |
| 310 |
|
| 311 |
How Django determines the lookups and transforms which are used |
| 312 |
=============================================================== |
| 313 |
|
| 314 |
In some cases you may wish to dynamically change which ``Transform`` or |
| 315 |
``Lookup`` is returned based on the name passed in, rather than fixing it. As |
| 316 |
an example, you could have a field which stores coordinates or an arbitrary |
| 317 |
dimension, and wish to allow a syntax like ``.filter(coords__x7=4)`` to return |
| 318 |
the objects where the 7th coordinate has value 4. In order to do this, you |
| 319 |
would override ``get_lookup`` with something like:: |
| 320 |
|
| 321 |
class CoordinatesField(Field): |
| 322 |
def get_lookup(self, lookup_name): |
| 323 |
if lookup_name.startswith("x"): |
| 324 |
try: |
| 325 |
dimension = int(lookup_name.removeprefix("x")) |
| 326 |
except ValueError: |
| 327 |
pass |
| 328 |
else: |
| 329 |
return get_coordinate_lookup(dimension) |
| 330 |
return super().get_lookup(lookup_name) |
| 331 |
|
| 332 |
You would then define ``get_coordinate_lookup`` appropriately to return a |
| 333 |
``Lookup`` subclass which handles the relevant value of ``dimension``. |
| 334 |
|
| 335 |
There is a similarly named method called ``get_transform()``. ``get_lookup()`` |
| 336 |
should always return a ``Lookup`` subclass, and ``get_transform()`` a |
| 337 |
``Transform`` subclass. It is important to remember that ``Transform`` |
| 338 |
objects can be further filtered on, and ``Lookup`` objects cannot. |
| 339 |
|
| 340 |
When filtering, if there is only one lookup name remaining to be resolved, we |
| 341 |
will look for a ``Lookup``. If there are multiple names, it will look for a |
| 342 |
``Transform``. In the situation where there is only one name and a ``Lookup`` |
| 343 |
is not found, we look for a ``Transform`` and then the ``exact`` lookup on that |
| 344 |
``Transform``. All call sequences always end with a ``Lookup``. To clarify: |
| 345 |
|
| 346 |
- ``.filter(myfield__mylookup)`` will call ``myfield.get_lookup('mylookup')``. |
| 347 |
- ``.filter(myfield__mytransform__mylookup)`` will call |
| 348 |
``myfield.get_transform('mytransform')``, and then |
| 349 |
``mytransform.get_lookup('mylookup')``. |
| 350 |
- ``.filter(myfield__mytransform)`` will first call |
| 351 |
``myfield.get_lookup('mytransform')``, which will fail, so it will fall back |
| 352 |
to calling ``myfield.get_transform('mytransform')`` and then |
| 353 |
``mytransform.get_lookup('exact')``. |