Trying to write a case-insensitive exclude failing in unexpected way

Using Django Using the ORM
1

Hey!

I’m writing an “API” for one of my app so that other users can run some queries against the model without having direct access to the service / database.

One of the ways I’m trying to make this “easier” on the user is to have most filtering being case-insensitive. And I need both a filter and an exclude version of this.

My understanding is that the official stance of Django for now is that having a iin (similar to iexact or iregex, …) is not deemed useful.
The solution I’ve found online is to do something like this:

from django.db.models.functions import Lower

Model.objects.annotate(lowercase=Lower("field")).filter(lowercase__in=["Value1", "Value2"])

and this seem to work. But the exclude version fails in an unexpected way.

Namely, when the field is nullable, and results in a None value, it’s being discarded.

Model.objects.annotate(lowercase=Lower("field")).exclude(lowercase__in=["Value1".lower(), "Value2".lower()])

results in any record where field is null (and as a result Lower("field") to also be) to just not return here. Which is not my expectation. As the “normal” ...exclude(field__in=["value1"]) does return these records.

Any idea of what I’m doing wrong?
Thank you

PS: In my app, field is actually a foreign key so it looks more like lowercase=Lower("foreignkey__name"). But from my testing the ForeignKey is not the differentiator here. I get the exact same result on a CharField that has None values.

2

I’m pretty sure you hit this particular issue @Xaelias

https://code.djangoproject.com/ticket/32398

The TL;DR is that while the ORM is smart enough to properly handle nulls and JOIN reuse when filtering directly against field it’s not the case when dealing with expressions references.

Some efforts were invested in trying to solve the problem in the past and there has been some recent progress towards making filter building smarter in this regard but until this lands your best bet is to either to:

Register __lowercase transform which could also perform the .lower() automatically for you without an annotation (e.g. field__lowercase__in=[...]).

Or to specify the null handling yourself by doing filter(Q(field=None) | ~Q(lowercase__in=["Value1".lower(), "Value2".lower()])

3

Ah thanks my search failed to yield this specific issue. I’ll look into the solutions you mention and see if they work for my use case.

4

Just reporting back, doing the __lower transform seem to work perfectly.

Adding the following lines:

from django.db.models import CharField

CharField.register_lookup(Lower)

and doing something like:

def exclude_case_insensitive(self, queryset, name, value):
        return queryset.exclude(**{f"{name}__lower__in": [e.lower() for e in value]})

(I’m working with django-filter for this)
seem to be producing the expected results.

Thanks again!

1 Like