Defining bilateral lookup transformations

[Thomas C]

With the current Transform API, transformations are applied to the left-hand side of the lookup only.

For instance, if you define some unaccent Transform performing the SQL operation UNACCENT(...), the query below won't return an Author with first name Jérémie. Actually, it won't ever return anything.

Author.objects.filter(first_name__unaccent__iexact="Jérémie")

I think it would be great to be able to define custom transformations which would apply to both sides of the lookup. This would be particularly helpful when you want to perform some lookup insensitive to some SQL transformation (unaccent or other).

[Anssi Kääriäinen]

I am going to mark this as accepted assuming the API is opt-in. We already have a precedence of doing this for lower lookup. We could actually remove the lower lookup from Django, and replace it with a bilateral transform.

[Tim Graham]

Needs a more thorough ORM review, but looking good to me.

[Thomas C]

I updated the PR with a specific error message when trying to apply a bilateral transformation to a nested QuerySet (see ​https://github.com/django/django/pull/3225/files#diff-b6b218ec29b7fb6a7d89868a94bfc73eR94) — thanks to akaariai for pointing out this case.

We cannot handle a nested QuerySet as we would have to "push" our transformation into that QuerySet, which would require some tricky code for a very unusual use case.

[Anssi Kääriäinen <akaariai@…>]

In 00aa562884a418c4ee20e223ab82c3455997ee7d:

Fixed #23493 -- Added bilateral attribute to Transform