Is there a way to make a case-insensitive IN request in Django?

Question

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

This is a problem because sometimes I need to do a search where I am sure that the case will be wrong.

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

Is there anything I can do to handle this? Ask people to come up with this hack?

Answer 1

I worked on this by making the MySQL database itself case-insensitive. I doubt that people from Django are interested in adding this as a function or providing documents on how to provide their own search in the field (provided that this is possible even without providing code for each db backend).

Here's one way to do this, admittedly, awkwardly.

 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) 

Answer 2

If this does not lead to conflicts, a possible workaround can convert the strings to upper or lower case when saving the object or to filter .

Answer 3

Here is a solution that does not require DB data. It also does filtering on the side of the database processor, which means 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) 

Another effective solution is to use an additional version with the rather portable raw-SQL lower() function:

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

Answer 4

Another solution - albeit a crude one - should include various cases of source strings in the list argument in the "in" filter. For example: instead of ['a', 'b', 'c'] use ['a', 'b', 'c', 'A', 'B', 'C'] instead.

Here is 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 

Answer 5

If your database is MySQL, Django handles IN queries seamlessly. Although I'm not sure about others

Change 1:

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

will give the following result in which the name of the city

Tokyo or TOKIO or tokio or Paris or PARIS or paris

Answer 6

Litle in a more elegant way would be the following:

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

Answer 7

A search using the Q object can be constructed so that it gets into 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)