Queryset: Compare a field with a substring of another field of the same model

Question

I am trying to check if the first 3 characters of a Charfield (charfield_1) are similar to another Charfield (charfield_2) of the same model.

Tried:

User.objects.filter(charfield_2__startswith=Substr('charfield_1', 1, 3))

Tried using F and Func without any success. I keep getting:

django.db.utils.DataError: invalid input syntax for integer: "1%"
LINE 1: ...CE(REPLACE((SUBSTRING("model_name"."charfield_2", '1%', 3)),...

Any idea how to make this work? I would like a solution using the ORM to avoid performance issues.

Update:

After checking the query generated by the ORM and the error message, it looks like the second Substr parameter is replaced by a non integer when I am using startswith or contains lookup expression.

ex: Substr('charfield_1', 1, 3) is replace by Substr('charfield_1', '%1%', 3)

I am using version 2.0.2.

A ticket has been opened and accepted : https://code.djangoproject.com/ticket/29155

Answer 1

Strange error, looks like a bug of Django ? At home, using 1.11, this works:

from django.db.models.functions import Substr

User.objects.annotate(f1=Substr('charfield_1', 1, 3), f2=Substr('charfield_2', 1, 3)).filter(f1=F('f2'))

Answer 2

You can write a User method (or a function if you're using the built-in User class) that compare your fields.

For example with a method:

class User:
    def has_same_start(self):
       return self.charfield_1[0:3] == self.charfield_2[0:3]

Then for your queryset:

all_users = User.objects.all()

# method1 - returns a list of User objects
my_selected_users = [user if user.has_same_start() for user in all_users]  

# method2 - returns a queryset
my_selected_users = User.objects.filter(pk__in=[user.pk if user.has_same_start() for user in all_users])

I don't say it's the best solution. Just one solution, but maybe there is better, in terms of performance. However, when I need to do some tricky comparisons, I prefer using separated methods sothat unit testing is easier.