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

Question

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

I tried:

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

Tried to use 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 the solution to use ORM to avoid performance issues.

Update:

After checking the query generated by ORM and the error message, it seems that the second Substr parameter is replaced with non-integer when I use startswith or contains a search expression.

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

I am using version 2.0.2.

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

Answer 1

Strange error, similar to Django error? At home, using 1.11, this works:

 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 function if you use the built-in User class) that compare your fields.

For example, using the method:

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

Then for your request:

 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 do not think this is the best solution. Just one solution, but maybe better in terms of performance. However, when I need to make some complicated comparisons, I prefer to use separate methods, since unit testing is easier.

Answer 3

After checking the query generated by ORM and the error message, it looks like the second Substr parameter is replaced with a non-integer one when I use the startswith or contains search expression.

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

I am going to report an error on django. By the way, I am using version 2.0.2.