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.
我试图检查Charfield(charfield_1)的前3个字符是否与同一模型的另一个Charfield(charfield_2)相似。
Tried:
User.objects.filter(charfield_2__startswith=Substr('charfield_1', 1, 3))
Tried using F and Func without any success. I keep getting:
使用F和Func尝试没有任何成功。我一直在:
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.
知道如何使这项工作?我想要一个使用ORM的解决方案来避免性能问题。
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.
检查ORM生成的查询和错误消息后,当我使用startswith或包含查找表达式时,看起来第二个Substr参数被非整数替换。
ex: Substr('charfield_1', 1, 3) is replace by Substr('charfield_1', '%1%', 3)
例如:Substr('charfield_1',1,3)被Substr替换('charfield_1','%1%',3)
I am using version 2.0.2.
我使用的是2.0.2版。
A ticket has been opened and accepted : https://code.djangoproject.com/ticket/29155
已打开并接受票证:https://code.djangoproject.com/ticket/29155
3 个解决方案
#1
3
Strange error, looks like a bug of Django ? At home, using 1.11, this works:
奇怪的错误,看起来像Django的错误?在家里,使用1.11,这工作:
User.objects.annotate(f1=Substr('charfield_1', 1, 3), f2=Substr('charfield_2', 1, 3)).filter(f1=F('f2'))
#2
2
You can write a User method (or a function if you're using the built-in User class) that compare your fields.
您可以编写比较字段的User方法(或者如果您使用的是内置User类的函数)。
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:
然后为您的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.
我不是说这是最好的解决方案。只有一个解决方案,但在性能方面可能还有更好的解决方案。但是,当我需要进行一些棘手的比较时,我更喜欢使用分离的方法,以便更容易进行单元测试。
#3
0
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.
检查ORM生成的查询和错误消息后,当我使用startswith或包含查找表达式时,看起来第二个Substr参数被非整数替换。
ex: Substr('charfield_1', 1, 3) is replace by Substr('charfield_1', '%1%', 3)
例如:Substr('charfield_1',1,3)被Substr替换('charfield_1','%1%',3)
I am going to report the bug on django. I am using version 2.0.2 by the way.
我将报告django上的错误。我顺便使用版本2.0.2。
#1
3
Strange error, looks like a bug of Django ? At home, using 1.11, this works:
奇怪的错误,看起来像Django的错误?在家里,使用1.11,这工作:
User.objects.annotate(f1=Substr('charfield_1', 1, 3), f2=Substr('charfield_2', 1, 3)).filter(f1=F('f2'))
#2
2
You can write a User method (or a function if you're using the built-in User class) that compare your fields.
您可以编写比较字段的User方法(或者如果您使用的是内置User类的函数)。
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:
然后为您的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.
我不是说这是最好的解决方案。只有一个解决方案,但在性能方面可能还有更好的解决方案。但是,当我需要进行一些棘手的比较时,我更喜欢使用分离的方法,以便更容易进行单元测试。
#3
0
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.
检查ORM生成的查询和错误消息后,当我使用startswith或包含查找表达式时,看起来第二个Substr参数被非整数替换。
ex: Substr('charfield_1', 1, 3) is replace by Substr('charfield_1', '%1%', 3)
例如:Substr('charfield_1',1,3)被Substr替换('charfield_1','%1%',3)
I am going to report the bug on django. I am using version 2.0.2 by the way.
我将报告django上的错误。我顺便使用版本2.0.2。