Registering lookups on relation fields should be supported.

[Thomas]

Hello,

I have a model, let's call it Parent, with a field called object_id. I have another model, let's call it Child, which has a ForeignKey field called parent_object[_id] pointing to Parent.object_id. I need to do a lookup on Child where the FK starts with a certain character (it's a normalized value so, in the context of my app, it makes sense... also, I didn't design this schema and changing it is not a possibility ATM).

The problem is that if I do:

qs = Child.objects.filter(parent_object_id__startswith='c')

I get:

django.core.exceptions.FieldError: Related Field got invalid lookup: startswith

The only way I could make it work is:

qs = Child.objects.filter(parent_object__object_id__startswith='c')

but it forces a join between the table and the view and that's a no-no in my case (way too costly).

Here's the MCVE (tested on Python 3.9 + Django 4.0.7 and Python 3.10 + Django 4.1.1):

import django
django.setup()
from django.db import models


class Parent(models.Model):
    class Meta:
        app_label = 'test'

    object_id = models.CharField('Object ID', max_length=20, unique=True)


class Child(models.Model):
    class Meta:
        app_label = 'test'

    parent_object = models.ForeignKey(
        Parent, to_field='object_id', related_name='%(class)s_set', on_delete=models.CASCADE
    )


if __name__ == '__main__':
    qs = Child.objects.filter(parent_object_id__startswith='c')  # fails with `FieldError: Related Field got invalid lookup: startswith`
    qs = Child.objects.filter(parent_object__object_id__startswith='c')  # works but forces a costly join

And the error:

Traceback (most recent call last):
  File "/opt/src/orm_test.py", line 26, in <module>
    qs = Child.objects.filter(parent_object_id__startswith='c')
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/query.py", line 1420, in filter
    return self._filter_or_exclude(False, args, kwargs)
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/query.py", line 1438, in _filter_or_exclude
    clone._filter_or_exclude_inplace(negate, args, kwargs)
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/query.py", line 1445, in _filter_or_exclude_inplace
    self._query.add_q(Q(*args, **kwargs))
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/sql/query.py", line 1532, in add_q
    clause, _ = self._add_q(q_object, self.used_aliases)
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/sql/query.py", line 1562, in _add_q
    child_clause, needed_inner = self.build_filter(
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/sql/query.py", line 1478, in build_filter
    condition = self.build_lookup(lookups, col, value)
  File "/opt/src/venv/lib/python3.10/site-packages/django/db/models/sql/query.py", line 1292, in build_lookup
    raise FieldError(
django.core.exceptions.FieldError: Related Field got invalid lookup: startswith

Thanks for your help,

Regards,

[Mariusz Felisiak]

Thanks for the report. Django 4.2 (cd1afd553f9c175ebccfc0f50e72b43b9604bd97) allows ​registering lookups per field instances, so you will be able to register __startswith for parent_object_id, e.g.

parent_field = Child._meta.get_field("parent_object_id")
with register_lookup(parent_field, StartsWith):
    Child.objects.filter(parent_object_id__startswith='c')

Duplicate of #29799.

[Alex Morega]

Also, Thomas, it makes sense to assume there is a JOIN in the second queryset, but apparently there isn't:

>>> print(Child.objects.filter(parent_object__object_id__startswith='c').query)
SELECT "test_child"."id", "test_child"."parent_object_id" FROM "test_child" WHERE "test_child"."parent_object_id" LIKE c% ESCAPE '\'

[Thomas]

@Mariusz Felisiak: Thanks for the heads-up, I hadn't found that ticket in my searches.

@Alex Morega: Thank you. I should have checked the SQL of my example for the join. I relied on my findings on my existing code base which uses parent_object__object_id__startswith to circumvent the RelatedField lookup problem. I have the join there but it must be coming from somewhere else.

[Simon Charette]

This also has similarities with this very old ticket https://code.djangoproject.com/ticket/2331#comment:7 so it's likely that parent_object_id__startswith='c' was actually never supported which is surprising to me.

[Mariusz Felisiak]

I noticed that registering transforms on related fields doesn't work at all as we have ​a guard that seems completely unnecessary. A regression test:

tests/queries/tests.py

@@ -1621 +1621 @@
             date_obj,
         )
 
+    def test_related_transform(self):
+        from django.db.models.functions import ExtractYear
+        from django.test.utils import register_lookup
+
+        date_obj = DateTimePK.objects.create()
+        extra_obj = ExtraInfo.objects.create(info="extra", date=date_obj)
+        fk_field = ExtraInfo._meta.get_field("date")
+        with register_lookup(fk_field, ExtractYear):
+            self.assertSequenceEqual(
+                ExtraInfo.objects.filter(date__year=2022),
+                [extra_obj],
+            )
+
     def test_ticket10181(self):
         # Avoid raising an EmptyResultSet if an inner query is probably
         # empty (and hence, not executed).

We could consider this a release blocker after 10178197d57476f69688d4535e550a1ea3a5eac5 🤔.

[Simon Charette]

I'm not sure I understand the rationale behind making this a release blocker as lookups on related fields were never supported? I remember having to remove this check a while ago when trying to add support for a m2m__exists lookup to work around #10060 ​in a feature branch.

I think this a limitation we should lift with proper test coverage but I fail to see how it relates to 10178197d57476f69688d4535e550a1ea3a5eac5

[Mariusz Felisiak]

​PR

Agreed, it's not a release blocker.

[GitHub <noreply@…>]

In ce6230a:

Fixed #34015 -- Allowed filtering by transforms on relation fields.