As I was working on my website in Django I had a need for ordering a model based queryset on a custom SQL. Searching around the net I found this entry in the Django user group, but according that entry I have to create a suclass of the queryset and then a manager, in addition to other things. I didn’t like that solution so I continued looking around and finally came up with one that I think is cool and accomplishes the goal. Basically, we have to use the extra() method to create a new column based on custom SQL and then use that in the order by clause.
1 2 |
comments = Comment.objects.filter(node__id=node.id).extra( select={'thread_order': "SUBSTR(thread, 1, LENGTH(thread)-1)"}).order_by('thread_order') |
In this case I needed to exclude the last character of the ‘thread’ column, and that’s what the SUBSTR does. So, there you have it, a quick way to order model querysets by custom SQL.