skip to content

With Django's register_lookup(), how would you let queries filter a custom MoneyField by currency, as in total__currency='EUR', and why use a Transform?

level: seniorimportance: nice to knowfreq 20%

answer

  1. a value versus a condition
  2. chainable after the double underscore
  3. SQL template plus output_field
  4. register on the field class

basics

~20 s

Write a Transform subclass with lookup_name = 'currency', an SQL template extracting the code and output_field = CharField(), then register it with MoneyField.register_lookup(). A Transform yields a value, so exact, in and order_by chain after it.

solid answer

~30 s

A `Lookup` produces a boolean condition and ends the chain; a `Transform` produces a value, so other lookups can follow it. For `MoneyField`, stored as `"EUR 12.50"`, define `class CurrencyCode(Transform)` with `lookup_name = "currency"`, `template = "SUBSTR(%(expressions)s, 1, 3)"` and `output_field = models.CharField()`, then call `MoneyField.register_lookup(CurrencyCode)` in `models.py` or `AppConfig.ready()`. `total__currency="EUR"` becomes `total__currency__exact`, and `total__currency__in=[...]` and `order_by("total__currency")` work too. The `output_field` matters: without it the transform's output is a `MoneyField`, so `"EUR"` would be sent through `MoneyField.get_prep_value()` and fail to parse. An index on the expression may be needed for speed.

code

python · 17 lines
python
from django.db import models
from django.db.models import Transform

from billing.fields import MoneyField


@MoneyField.register_lookup
class CurrencyCode(Transform):
    lookup_name = "currency"
    template = "SUBSTR(%(expressions)s, 1, 3)"
    output_field = models.CharField()


# Invoice.total is a MoneyField storing text such as 'EUR 12.50'
Invoice.objects.filter(total__currency="GBP")
Invoice.objects.filter(total__currency__in=["EUR", "GBP"])
Invoice.objects.order_by("total__currency", "number")

go deeper

for a junior

Know that Django lets you add your own double-underscore lookups and that they are registered on a field class.

for a middle

Explain Lookup versus Transform, and show a Transform with lookup_name, a template and output_field chaining into exact and in.

for a senior

Anticipate the output_field trap, registration timing across processes, per-instance registration and the index an expression filter needs on large tables.

for a principal

Judge when custom lookups are a clean API over a packed column and when they are a sign the data should be split into native columns.

## The problem `MoneyField` stores an amount and a currency as one text column, `"EUR 12.50"`. Finance wants every invoice in pounds. `Invoice.objects.filter(total__startswith="GBP")` works by accident of the format, but it leaks the storage layout into every call site and cannot be chained into `__in` or used in `order_by()`. Django's **custom lookup API** lets the field offer `total__currency` instead. ## `Lookup` versus `Transform` Both live in `django.db.models` and are registered the same way, but they produce different things: | | `Lookup` | `Transform` | |---|---|---| | Produces | a boolean SQL condition | an SQL value expression | | Chaining | ends the lookup path | other lookups and transforms can follow | | Default when last | used as written | Django appends `exact` | | Usable in `order_by()` | no | yes | | Example | `total__ne=...` (a custom "not equal") | `total__currency`, `change__abs__lt` | Extracting the currency is a value, so it is a **`Transform`**. `total__currency="EUR"` is then read as `total__currency__exact="EUR"`, and `total__currency__in=["EUR", "GBP"]` works for free. ## Writing the transform A `Transform` is a one-argument SQL function expression. The pieces: - **`lookup_name`** — the name after the double underscore; it must not contain `__`. - **`function` or `template`** — `function = "UPPER"` renders `UPPER(col)`; a class-level `template` such as `"SUBSTR(%(expressions)s, 1, 3)"` gives full control. `SUBSTR` exists on all four built-in backends. - **`output_field`** — the field type of the result. This decides which lookups may follow and how their arguments are prepared. ## Why `output_field` matters here If `CurrencyCode` omits `output_field`, the transform's output field is the input's: a `MoneyField`. Then `total__currency="EUR"` asks `MoneyField.get_prep_value("EUR")` to prepare the argument; that calls `to_python()`, which tries to parse `"EUR"` as a money value and raises `ValidationError`. Declaring `output_field = models.CharField()` makes the following lookup behave as it would on a plain `CharField`: the argument stays `"EUR"`, and `iexact` or `in` follow naturally. ## Registering it 1. **On the field class** — `MoneyField.register_lookup(CurrencyCode)` makes `__currency` available on every `MoneyField`, including subclasses, because lookup resolution walks parent classes. 2. **On one field instance** — `Invoice._meta.get_field("total").register_lookup(CurrencyCode)` scopes it to that field; instance registrations take precedence over class ones. 3. **Timing** — registration must happen before any queryset uses the name: in the module that defines the field, in `models.py`, or in `AppConfig.ready()`. 4. **Decorator form** — `@MoneyField.register_lookup` above the class definition does the same. Registering on the base `Field` makes a lookup available on every field in the project; do that only for genuinely generic lookups. ## Checking and debugging it A transform is ordinary code and deserves the same checks as any field method: - **See the SQL.** `print(Invoice.objects.filter(total__currency="GBP").query)` shows the rendered `SUBSTR(...)` condition, which is the fastest way to confirm the template and the parameter. - **Confirm registration.** `MoneyField.get_lookups()` returns the registered names; `currency` should appear in it. - **Read the error.** Using the name before registration, or on a field that lacks it, raises `FieldError: Unsupported lookup 'currency' for MoneyField or join on the field not permitted`. In practice that usually means the module holding the registration was never imported in that process. - **Test chaining.** One test each for `total__currency`, `total__currency__in` and `order_by("total__currency")` exercises the output field as well as the template. If the storage format changes — say the currency moves to the end of the text — only the template changes, and every call site keeps working. That is the real value of the transform: the storage layout stays private to the field. ## Costs and limits - **Indexes.** `SUBSTR(total, 1, 3) = 'EUR'` cannot use a plain index on `total`; a large table needs an index on the same expression, declared in the model's `Meta.indexes`. - **Backend differences.** Where SQL differs by vendor, a transform can define `as_postgresql()`, `as_mysql()` and similar methods; Django picks the one matching the connection. - **NULL rows.** For a nullable `MoneyField`, `SUBSTR` of `NULL` is `NULL`, so null totals match no currency; `total__isnull=True` finds them. - **It is a sign.** Needing several transforms to reach inside one column is evidence that the parts want their own columns.

  • When would you write a Lookup instead of a Transform?
    When the operation is itself a condition that nothing should follow, such as a custom `ne` (not equal) or a vendor-specific operator. A `Lookup` implements `as_sql()` with `process_lhs()` and `process_rhs()` and returns the boolean SQL; it cannot be chained or ordered by.
  • Where should register_lookup() be called so the lookup always exists?
    Somewhere imported before the first query uses it: the module defining the field, `models.py`, or the app's `AppConfig.ready()`. Registering it inside a view or a management command leaves it missing in other processes, where the query raises `FieldError` for an unsupported lookup.

saying these in an interview costs you the question

  • A custom Lookup can be chained with __in just like a Transform.
  • output_field on a Transform is cosmetic and does not change later lookups.
  • register_lookup() only works on built-in Django field classes.
  • Registering a lookup on one field instance changes every field of that class.
  • The database can use a plain column index for the SUBSTR comparison.