MC wrote:
> Problem:
> I order dates and null values in ascending order under MySQL. To my
> surprise, null values are listed first, then actual dates. Turns out
> that in MySQL, null values are LOWER than non-null values, whereas
> Postgresql is the reverse. As outlined here
> http://troels.arvin.dk/db/rdbms/#select-order_by, it seems the only way
> to reverse the order is by prepending a - sign and switching from ASC
> to DESC. Unfortunately, this is the same syntax Django uses for
> ordering!
>
> Solution:
> I couldn't think of an easy solution through the model API or using
> custom managers. I suppose I could write custom SQL, but it would be
> nicer to use the model API. I could use a default value like the year
> 3000 and filter for display. Any suggestions? Thanks for your help!
You could use extra() to add an expression column and then order by
that. Something like:
>>> s = Strategy.objects.all().extra(select={'mycol': 'case when name is null
>>> then 1 else 0 end'}).order_by('mycol', 'name')
This adds a new column to the select using the case expression and
aliased to 'mycol' which you can then reference in the order_by()
clause. By making null's have a value of 1, it forces them to be sorted
below non-nulls.
-Dave
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
