Short question: Is there a way in Django to find the next row, based on the alphabetical order of some field, in a case-insensitive way?
Long question: I have some words in the database, and a detail view for them. I would like to be able to browse the words in alphabetical order. So I need to find out the id of the previous and next word in alphabetical order. Right now what I do is the following (original is the field that stores the name of the word):
class Word(models.Model):
original = models.CharField(max_length=50)
...
def neighbours(self):
"""
Returns the words adjacent to a given word, in alphabetical order
"""
previous_words = Word.objects.filter(
original__lt=self.original).order_by('-original')
next_words = Word.objects.filter(
original__gt=self.original).order_by('original')
previous = previous_words[0] if len(previous_words) else None
next = next_words[0] if len(next_words) else None
return previous, next
The problem is that this does a case-sensitive comparison, so Foo appears before bar, which is not what I want. To avoid this problem, in another view - where I list all words, I have made use of a custom model manager which adds an extra field, like this
class CaseInsensitiveManager(models.Manager):
def get_query_set(self):
"""
Also adds an extra 'lower' field which is useful for ordering
"""
return super(CaseInsensitiveManager, self).get_query_set().extra(
select={'lower': 'lower(original)'})
and in the definition of Word I add
objects = models.Manager()
alpha = CaseInsensitiveManager()
In this way I can do queries like
Word.alpha.all().order_by('lower')
and get all words in alphabetical order, regardless of the case. But I cannot do
class Word(models.Model):
original = models.CharField(max_length=50)
...
objects = models.Manager()
alpha = CaseInsensitiveManager()
def neighbours(self):
previous_words = Word.objects.filter(
lower__lt=self.lower()).order_by('-lower')
next_words = Word.objects.filter(
lower__gt=self.lower()).order_by('lower')
previous = previous_words[0] if len(previous_words) else None
next = next_words[0] if len(next_words) else None
return previous, next
Indeed Django will not accept field lookups based on extra fields. So, what am I supposed to do (short of writing custom SQL)?
Bonus questions: I see at least to more problems in what I am doing. First, I'm not sure about performance. I assume that no queries at all are performed when I define previous_words and next_words, and the only lookup in the database will happen when I define previous and next, yielding a query which is more or less
SELECT Word.original, ..., lower(Word.original) AS lower
WHERE lower < `foo`
ORDER BY lower DESC
LIMIT 1
Is this right? Or am I doing something which will slow down the database too much? I don't know enough details about the inner workings of the Django ORM.
The second problem is that I actually have to cope with words in different languages. Given that I know the language for each word, is there a way to get them in alphabetical order even if they have non-ASCII characters. For instance I'd want to have méchant, moche in this order, but I get moche, méchant.
