Debian Codegraph @

docs/howto/custom-lookups.txt

raw
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')``.