How to use Lower() to sort a queryset in reverse order

Question

If I don't use the Lower() function when sorting a queryset, then my queryset sorts uppercase and lowercase values separately. So if my queryset contains "Apples, Bananas, cherries, Oranges" and I use order_by('name') then I get: "Apples, Bananas, Oranges, cherries". If I use order_by(Lower('name')) then all works fine.

My problem is that my view sorts using MyModel.objects.all().order_by(order_by) where order_by is set using a user specified GET variable. Lower() errors when the GET variable tries to sort in reverse order, eg order_by='-name'

I tried the following code but I get a traceback error when order_by='-name', but works fine when order_by='name': MyModel.objects.all().order_by(Lower(order_by))

I then tried something smarter, but now when order_by='-name' then the queryset stops sorting alphabetically and just sorts using the object ID. But sorts fine when order_by='name'.

Views.py

@login_required
def merchantgroups_show_all(request):
    order_by = request.GET.get('order_by', 'name')




    from django.db.models.functions import Lower
    if order_by[0]=='-':
        order_by = order_by[1:]
        merchant_groups = MerchantGroup.objects.all().order_by('-'+Lower(order_by))
    else:
        merchant_groups = MerchantGroup.objects.all().order_by(Lower(order_by))




    paginator = Paginator(merchant_groups,20)
    page = request.GET.get('page')
    merchant_groups2 = paginator.get_page(page)
    return render(request, 'monzo/merchantgroups_show_all.html', {'merchant_groups': merchant_groups2,})

Any ideas on how to get Lower() to work when the GET variable order_by starts with a negative value?

I suggest you either apply `lower()` or `upper()` before storing the value in the database, this way you avoid the issue you have now — Sander Bakker, May 19 '20 at 13:30
related: https://stackoverflow.com/q/9834038, https://stackoverflow.com/q/3409047 — djvg, Sep 20 '22 at 09:44

score 0 · Accepted Answer · answered May 19 '20 at 13:55

0

I think you'll need to annotate with the lowered name and order by that:

merchant_groups = MerchantGroup.objects.all().annotate(lower_name=Lower("name")).order_by("-lower_name")

answered May 19 '20 at 13:55

RHSmith159

1,823
9
16

djvg · Answer 2 · 2022-09-20T12:31:55.150

0

Django's queryset API actually has a reverse() method, so you can do the following:

MyModel.objects.order_by(Lower('name')).reverse()

The resulting SQL looks something like this (removed table names for clarity):

SELECT ... FROM "myapp_mymodel" ORDER BY LOWER("name") DESC

edited Sep 20 '22 at 12:31

answered Sep 20 '22 at 09:09

djvg

11,722
5
72
103

score 0 · Answer 3 · answered Dec 18 '22 at 21:34

Actually, since Lower implements Django's Expression API you could do something like this to save the unnecessary annotation:

MerchantGroup.objects.all().order_by(Lower('name').desc())

# and if the model is nullable... and you wanted them at the end:
MerchantGroup.objects.all().order_by(Lower('name').desc(nulls_last=True))

How to use Lower() to sort a queryset in reverse order

3 Answers3