Ordering case insensitive in django-tables2

Question

First post in this awesome community that I have been reading a long time ago :)

I've encountered with a problem when using this fantastic library "django-tables2". When I'm ordering a column by a CharField, it does a case sensitive ordering resulting unexpected behaviour like this:

Iago
Pablo
iago

I want to be ordered in a more natural way:

Iago
iago
Pablo

This is my simplified code for the table:

class Inquiry(models.Model):
    ...
    contact_last_name = models.CharField(max_length=50)
    ...

class Hometable(tables.Table):
    contact_last_name = tables.Column(verbose_name="Contact", order_by=('contact_last_name'))
    class Meta:
        model = Inquiry
        fields= ('contact_last_name',)

I know in Django 1.8 there is a built-in function Lower to make a insensitive order_by, but it doesn't work with django tables:

contact_last_name = tables.Column(verbose_name="Contact", order_by=(Lower('contact_last_name')))

It results in an exception:

TypeError at /
    'Lower' object is not iterable

Has anyone done anything similar with django-tables2?

Thank you!

UPDATE: The solution is to make a annotation in the view with the lowercase fields, an then will be available to order by in the table.

class Inquiry(models.Model):
    ...
    contact_last_name = models.CharField(max_length=50)
    ...

class Hometable(tables.Table):
    contact_last_name = tables.Column(verbose_name="Contact", order_by=('contact_last_name_lower'))
    class Meta:
        model = Inquiry
        fields= ('contact_last_name',)

And make the proper annotation in the queryset when configuring the table as Alasdair purposed:

inquiries = inquiries.annotate(contact_last_name_lower=Lower('last_name'))
my_table = Hometable(inquiries)

The 'not iterable' error is because you have missed out a comma in (Lower('contact_last_name'),). It might not work once after you've fixed that, but the error message will be different. — Alasdair
If the existing answers are not complete, it's better to add an answer to your own question, rather than including the answer in the question itself. — Alasdair

Mat Mat · Accepted Answer · 2019-03-17T22:56:33

Based on https://django-tables2.readthedocs.io/en/latest/pages/ordering.html#table-order-foo-methods you need to add a table.order_FOO() method and end up with something like this:

class Hometable(tables.Table):
    contact_last_name = tables.Column(verbose_name="Contact", order_by=('contact_last_name'))

    def order_contact_last_name (self, QuerySet, is_descending):
        QuerySet = QuerySet.annotate(
            field_lower=Func(F('contact_last_name '), function='LOWER')
        ).order_by(('-' if is_descending else '') + 'field_lower')
        return (QuerySet, True)

    class Meta:
        model = Inquiry
        fields= ('contact_last_name',)

This should work when you click column headers to order as well as the initial ordering.

Ordering case insensitive in django-tables2

2 Answers