django select max field from mysql when column is varchar

Question

Hi,

Using Django 1.1, I am trying to select the maximum value from a varchar column (in MySQL.) The data stored in the column looks like:

9001
9002
9017
9624
10104
11823

(In reality, the numbers are much bigger than this.)

This worked until the numbers incremented above 10000:

Feedback.objects.filter(est__pk=est_id).aggregate(sid=Max('sid'))

Now, that same line would return 9624 instead of 11823.

I'm able to run a query directly in the DB that gives me what I need, but I can't figure out the best way to do this in Django. The query would be:

select max(sid+0) from Feedback;

Any help would be much appreciated.

Thanks!

Answer 1

In the spirit of "any help would be much appreciated", you should figure out why it stopped working inside Django (but apparently not inside MySQL) - at 10,000.

What is the query that is being generated? See this question for how to find that out.

I suspect it is because you're adding the +0 to make the sort numeric in your query. I don't think Django supports this naturally, so you have two options:

• People will undoubtedly want to know why you're storing a number and asking for the maximum of it in a VARCHAR column. You could change the column to a numeric data type.
• You could do what you have to do whenever you want to make some custom SQL call and Django doesn't yet support it: write your own raw SQL.
• Edit: You could also patch Django, but this might be a MySQL specific thing, so option #2 is probably your best bet.