"Related Field got invalid lookup: startswith" on CharField ForeignKey

[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/_sandbox/django_bug.py", line 23, in <module>
    qs = Child.objects.filter(media_object_id__startswith='c')
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/query.py", line 1071, in filter
    return self._filter_or_exclude(False, args, kwargs)
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/query.py", line 1089, in _filter_or_exclude
    clone._filter_or_exclude_inplace(negate, args, kwargs)
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/query.py", line 1096, in _filter_or_exclude_inplace
    self._query.add_q(Q(*args, **kwargs))
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1502, in add_q
    clause, _ = self._add_q(q_object, self.used_aliases)
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1532, in _add_q
    child_clause, needed_inner = self.build_filter(
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1448, in build_filter
    condition = self.build_lookup(lookups, col, value)
  File "/opt/venv/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1262, in build_lookup
    raise FieldError(
django.core.exceptions.FieldError: Related Field got invalid lookup: startswith

Thanks for your help,

Regards,