Django Queryset：在过滤之前，先在SQL中投射VARCHAR字段 [英] Django Queryset: Cast VARCHAR field in SQL before filtering

问题描述

我有一个表，我无法控制，但需要从中选择。 建筑字段是一个varchar，并且在我的（非管理）django模型中也定义了这样一个。但是从该表中选择时，它应该被视为整数（有诸如000100之类的值，甚至最后是空格）。

我需要一个简单的过滤器像这样：

  .annotate（CastInt（building））filter（building__castint__exact = my_id）
  

只有问题是，CastInt不存在。怎么能达到这个目的？我正在看着.extra（）（我们不应再使用它，因为它已经不推荐使用）和RawSQL，但希望只使用Django ORM的解决方案，而不需要自定义的写入SQL？

编辑：目前正在使用此处中的hack（在评论中） ）：

  Address.objects.extra（where =（'CAST（TRIM（building）AS UNSIGNED）=％s' ），
 params =（building_no，））
  

工作，但丑陋。 >

编辑II：看到我接受的答案 - 使用我的第二好友，正则表达式。

解决方案

一样简单：使用正则表达式查询

  regex = r'^ 0 {0,4}％ s $ $％building_no 
 address = Address.objects.filter（building__iregex = regex）\ 
  

任何关于性能的提示欢迎...

I have a table that I cannot control, but need to select from it. The field "building" is a varchar, and also defined like this in my (non managed) django model. But it should be treated as integer, when selecting from that table (there are values like "000100", and even spaces at the end).

I need a simple filter like this:

.annotate(CastInt("building")).filter(building__castint__exact=my_id)

only problem is, CastInt does not exist. How could one achieve this? I was looking at .extra() (that we should no use anymore, as it's deprecated) and RawSQL, but hoping for a solution using only the Django ORM, without custom written SQL?

EDIT: currently using a hack from here (in the comments):

Address.objects.extra(where=('CAST(TRIM(building) AS UNSIGNED)=%s',),
                      params=(building_no, ))

works, but ugly.

EDIT II: See my accepted answer - using my second best friend, the regex.

解决方案

as simple as it gets: use a regex query

regex = r'^0{0,4}%s ?$' % building_no
address = Address.objects.filter(building__iregex=regex)\

any hints on performance welcome...

这篇关于Django Queryset：在过滤之前，先在SQL中投射VARCHAR字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！