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

问题:

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?

回答1:

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)

回答2:

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.

回答3:

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

回答4:

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

回答5:

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

回答6:

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