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

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 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)

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

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.

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])

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

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)

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]]

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])