id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34015	Registering lookups on relation fields should be supported.	Thomas	Mariusz Felisiak	"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:
{{{#!python
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:
{{{#!python
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):
{{{
#!python
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,"	New feature	closed	Database layer (models, ORM)	4.1	Normal	fixed	ORM lookup	Thomas Simon Charette AllenJonathan	Accepted	1	0	0	0	0	0