Is there any way to do a case-insensitive IN query in Django?

Question

Nearly every kind of lookup in Django has a case-insensitive version, EXCEPT in, it appears.

This is a problem because sometimes I need to do a lookup where I am certain the case will be incorrect.

Products.objects.filter(code__in=[user_entered_data_as_list])

Is there anything I can do to deal with this? Have people come up with a hack to work around this issue?

I find it peculiar. At least on MySql it seems that the __in lookup is case insensitive. — Anuvrat Parashar, Aug 11 '12 at 06:06

score 9 · Accepted Answer · answered Oct 12 '10 at 17:36

I worked around this by making the MySQL database itself case-insensitive. I doubt that the people at Django are interested in adding this as a feature or in providing docs on how to provide your own field lookup (assuming that is even possible without providing code for each db backend)

Here is one way to do it, admittedly it is clunky.

products = Product.objects.filter(**normal_filters_here)
results = Product.objects.none()
for d in user_entered_data_as_list:
    results |= products.filter(code__iexact=d)

Point Networks · Answer 2 · 2016-05-20T14:10:30.120

2

If your database is MySQL, Django treats IN queries case insensitively. Though I am not sure about others

Edit 1:

model_name.objects.filter(location__city__name__in': ['Tokio','Paris',])

will give following result in which city name is

Tokio or TOKIO or tokio or Paris or PARIS or paris

edited May 20 '16 at 14:10

answered May 20 '16 at 11:00

Point Networks

1,071
1
13
35

Can you explain a bit more? – Dieter Meemken May 20 '16 at 11:21
What I meant is, if you are using MYSQL database with Django ORM system then all IN queries are case insensitive. – Point Networks May 20 '16 at 14:03
To @DieterMeemken I have edited my answer with an example. – Point Networks May 20 '16 at 14:11

score 1 · Answer 3 · answered Dec 30 '13 at 03:53

Here is a solution that do not require case-prepared DB values. Also it makes a filtering on DB-engine side, meaning much more performance than iterating over objects.all().

def case_insensitive_in_filter(fieldname, iterable):
    """returns Q(fieldname__in=iterable) but case insensitive"""
    q_list = map(lambda n: Q(**{fieldname+'__iexact': n}), iterable)
    return reduce(lambda a, b: a | b, q_list)

The other efficient solution is to use extra with quite portable raw-SQL lower() function:

MyModel.objects.extra(
    select={'lower_' + fieldname: 'lower(' + fieldname + ')'}
).filter('lover_' + fieldname + '__in'=[x.lower() for x in iterable])

score 1 · Answer 4 · answered Mar 02 '10 at 04:00

1

If it won't create conflicts, a possible workaround may be transforming the strings to upper or lowercase both when the object is saved and in the filter.

answered Mar 02 '10 at 04:00

nilved

63
5

Unfortunately, the objects being looked up can be lowercase or mixed case, and it's not possible to restrict this. – Jordan Reiter Mar 03 '10 at 21:07

score 1 · Answer 5 · answered Nov 14 '15 at 22:17

Another solution - albeit crude - is to include the different cases of the original strings in the list argument to the 'in' filter. For example: instead of ['a', 'b', 'c'], use ['a', 'b', 'c', 'A', 'B', 'C'] instead.

Here's a function that builds such a list from a list of strings:

def build_list_for_case_insensitive_query(the_strings):
    results = list()
    for the_string in the_strings:
        results.append(the_string)
        if the_string.upper() not in results:
            results.append(the_string.upper())
        if the_string.lower() not in results:
            results.append(the_string.lower())
    return results

score 1 · Answer 6 · answered Jul 21 '19 at 13:02

A lookup using Q object can be built to hit the database only once:

from django.db.models import Q

user_inputed_codes = ['eN', 'De', 'FR']

lookup = Q()
for code in user_inputed_codes:
    lookup |= Q(code__iexact=code)
filtered_products = Products.objects.filter(lookup)

score 0 · Answer 7 · answered Sep 22 '12 at 12:29

0

A litle more elegant way would be this:

[x for x in Products.objects.all() if x.code.upper() in [y.upper() for y in user_entered_data_as_list]]

answered Sep 22 '12 at 12:29

user1690841

17
2

1

I don't see how this is more elegant. Products.objects.all() might return *thousands* of rows, and then you're going to filter it outside of the database and then return it as a list, not even as a query, which means you can't sort it (easily) or perform additional filters on it. Just realized you're also looping through `user_entered_data_as_list` *each time* so YEAH everyone, do **not** use this technique unless your table is very very small. – Jordan Reiter Sep 24 '12 at 15:14
Just did a test, your method takes around 200 times longer for a table with 30k+ records: http://pastebin.com/R78P0Pdq – Jordan Reiter Sep 24 '12 at 17:43
It's not effective, though you can try use generator instead of simple list. – sepulchered Oct 12 '12 at 10:45

score 0 · Answer 8 · answered Sep 26 '22 at 16:05

0

You can do it annotating the lowered code and also lowering the entered data

from django.db.models.functions import Lower

Products.objects.annotate(lower_code=Lower('code')).filter(lower_code__in=[user_entered_data_as_list_lowered])

answered Sep 26 '22 at 16:05

Hispar

1
1

Is there any way to do a case-insensitive IN query in Django?

8 Answers8

Linked