7

I have a list of strings and I would like to do an case-insensitive "in" lookup with Django ORM.

I know, that I can do this:

name_list = ['Alpha', 'bEtA', 'omegA']
q_list = map(lambda n: Q(name__iexact=n), name_list)
q_list = reduce(lambda a, b: a | b, q_list)
MyModel.objects.filter(q_list)

But maybe there is a simpler solutions with more modern Django ORM?

guettli
  • 25,042
  • 81
  • 346
  • 663
  • 1
    Another way is to use `iregex` as shown in [this](https://stackoverflow.com/questions/2667524/django-query-case-insensitive-list-match#answer-2667582) answer. – ikkuh Jun 20 '18 at 12:48

1 Answers1

1

IN operator(and other MySQL operators except binary comparisons) are case insensitive by default, unless you have changed the table collation. In most of the cases, you can simply use MyModel.objects.filter(name__in=name_list).

See https://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/ to know more about collation.

P.S. Avoid using map-reduce like this. If the name_list length is large enough, your entire mysql-server can get down.

Edit:

For PostgreSQL you can do the following: MyModel.objects.annotate(name_lower=Lower('name')).filter(name_lower__in=[name.lower() for name in name_list])

itstauq
  • 81
  • 6
  • I tested it with PostgreSQL and there the IN operator is case sensitive. This might be different on your database. AFAIK the SQL spec is not available for the public. Sad, I would like to know what the spec says. – guettli Aug 28 '18 at 09:28
  • Yes I was talking about MySQL. For PostgreSQL you can do the following: `MyModel.objects.annotate(name_lower=Lower('name')).filter(name_lower__in=[name.lower() for name in name_list])` – itstauq Aug 28 '18 at 11:30
  • @tahmend1994 please write your comment as answer, then I can up-vote it. – guettli Aug 28 '18 at 12:46
  • Unfortunately `__iexact` is *not* always the same a `lower(a) == lower(b)`. Some characters *have no* lowercase equivalent, (like the est-zet in German) – Willem Van Onsem Aug 29 '18 at 20:58
  • @WillemVanOnsem I tested it with simple ascii characters "foo" vs "FOO" and it did not work on PostgreSQL. I did not use "ß" – guettli Aug 30 '18 at 09:21