2

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.

Community
  • 1
  • 1
benzkji
  • 1,718
  • 20
  • 41
  • any specific reason to cast to int?? – Ajay Gupta Aug 18 '15 at 09:49
  • if "my_id" is 100, it will not select the row with "000100" – benzkji Aug 18 '15 at 09:50
  • 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 Aug 18 '15 at 09:51
  • simply writing int(string with prefixed 0) automatically converts it to a real number – Ajay Gupta Aug 18 '15 at 09:55
  • If you are using mysql, here's a way using raw sql, please check it out: http://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query – Shang Wang Aug 18 '15 at 15:08
  • the .extra() I've added in the question works quite well, but I wonder if there is a better and DRY'er way.. – benzkji Aug 18 '15 at 15:11
  • 1
    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 Aug 18 '15 at 15:21
  • completely agree. that's why I've not added it as my own response yet. also, extra is considered deprecated, so I'm not happy with this solution. – benzkji Aug 19 '15 at 08:31
  • @ShangWang I know how to do it in sql, and yes, I'm using MySQL. I'm looking for a better solution, using the Django ORM. – benzkji Aug 21 '15 at 18:18

2 Answers2

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

benzkji
  • 1,718
  • 20
  • 41
-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

Ajay Gupta
  • 1,285
  • 8
  • 22
  • 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) – benzkji Aug 18 '15 at 10:00
  • myfiltervalue value is what? – Ajay Gupta Aug 18 '15 at 10:21
  • 2
    I think @benzkji is asking for how to create a proper query, not how to convert between `int` and `str`. – Shang Wang Aug 18 '15 at 14:58
  • yes got it .. i am still looking for the solution.. @ShangWang: do you have any idea ? – Ajay Gupta Aug 18 '15 at 15:12