Django Queryset: Cast VARCHAR field in SQL before filtering

Question

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.

casting is not needed if it "works" without. I saw other questions where __contains was used, but this will not work, as a row could have "100100" as building field value. — benzkji
– benzkji, Commented Aug 18, 2015 at 9:51
simply writing int(string with prefixed 0) automatically converts it to a real number — Ajay Gupta
– Ajay Gupta, Commented Aug 18, 2015 at 9:55
I would argue that it's ugly or hacky. The purpose of extra, according to django docs is to add a hook for injecting specific clauses into the SQL generated by a QuerySet., there's always some cases that orm doesn't cover enough. Beware though, if you change your database backend the raw query might not work. — Shang Wang
– Shang Wang, Commented Aug 18, 2015 at 15:21

benzkji · Accepted Answer · 2015-10-21 12:35:29Z

2

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...

answered Oct 21, 2015 at 12:35

benzkji

1,95721 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajay Gupta · Accepted Answer · 2015-08-18 09:57:21Z

-3

try simple int(you string value)

for example:

str = "00100"
int(str)

will give 100 as integer value. same applies for

 str = "100100"
int(str)

gives 100100 as integer

answered Aug 18, 2015 at 9:57

Ajay Gupta

1,2858 silver badges22 bronze badges

4 Comments

benzkji Over a year ago

I already have a real int (that was cast, as you propose), that needs to filter my django queryset. The problem is the value in the db: it is "000100", and if I just do a Address.objects.filter(building=myfiltervalue), it doesnt not filter as I want (0 results)

Ajay Gupta Over a year ago

myfiltervalue value is what?

Shang Wang Over a year ago

I think @benzkji is asking for how to create a proper query, not how to convert between int and str.

Ajay Gupta Over a year ago

yes got it .. i am still looking for the solution.. @ShangWang: do you have any idea ?

Collectives™ on Stack Overflow

Django Queryset: Cast VARCHAR field in SQL before filtering

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related