# Elegant way \_\_in insensitive case

**URL:** <https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217>\
**Category:** Using the ORM\
**Created:** [December 16, 2023, 12:48pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217 "2023-12-16T12:48:44Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 12:48pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/1 "2023-12-16T12:48:44Z")

</div>

Hello all,

I am new on the forum (just 4 days) but I was already helped by KenWhitesell about Celery. Thank you so much.

This time I have a Django question how in elegant way made \_\_in search in insensitive case.

Let’s say I have a model Item

```auto
class Item(models.Model):
    year = models.CharField(max_length=4, blank=True, null=True)
    name = models.CharField(max_length=16, blank=True, null=True)

```

and also model Tag that contains short texts that describes Item

```auto
class Tag(models.Model):
    text = models.CharField(max_length=16, blank=True, null=True)
    item = models.ForeignKey(Item, on_delete=models.CASCADE)

```

How in elegant way search for Item based on Tags texts in insensitive way?

Here is View I have created. The ItemSearchListView gets query **q** which is a list of words, separated by comma, for instance _“Blue, white, Yellow, red”_. I split it into list and would like to make insensitive Tag search.

```auto
class ItemSearchListView(ListView):
    model = Item

    def get_queryset(self):
        q = self.request.GET.get('q')
        search_list = q.split(',')
        return Item.objects.filter(Q(Tag_set __text__ in=search_list)).distinct()

```

I found one solution on [stackoverflow.com](http://stackoverflow.com) but it is not elegant in my opinion and I believe can be done better, because it gets really messy and complicated when search by many different fields of Tag model for instance.

> <https://stackoverflow.com/questions/3923589/django-in-lowercase>

Any idea how to solve the really common problem? Maybe my approach is wrong and it should be done in different way that will allow to get the search in easy way.

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 3:55pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/2 "2023-12-16T15:55:32Z")

</div>

I have found the following:

NEWS: In Django 1.7+ it is possible to create your own lookups, so you can actually use `filter(name__iin=['name1', 'name2', 'name3'])` after proper initialization. See [documentation reference for details](https://docs.djangoproject.com/en/3.0/ref/models/lookups/).

I think I will need some hints how to make the “initialization” and how to use the Lookups API.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [December 16, 2023, 3:55pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/3 "2023-12-16T15:55:55Z")

</div>

Hello [Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl) 👋

At the database level I can think of ways of doing that.

The most obvious one is the solution in the link you provided; use a combination of `OR LOWER` like generated through reduction of `Q(field__iexact)` via `operator.or_`.

The second solution is [store the tag name in a case insensitive collation](https://docs.djangoproject.com/en/5.0/ref/models/fields/#django.db.models.CharField.db_collation) or [convert the column at run time](https://docs.djangoproject.com/en/5.0/ref/models/database-functions/#collate). The collation name will be dependant on the database engine you’re using, it would be `"nocase"` on SQLite and `"et-x-icu"` on Postgres.

If you use a case insensitive `db_collation` then you’ll be able to simply use `__in` otherwise, if you prefer a runtime solution, [defining a custom `__iin` (for case-insensitive lookup) that generates the proper expression](https://docs.djangoproject.com/en/5.0/howto/custom-lookups/) might make the solution more _elegant_.

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 4:03pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/4 "2023-12-16T16:03:48Z")

</div>

Thank you @charettes for your answer.  
I didn’t get what the db\_collation is to be honest. Is it automatic way to store values always in lower case in database?

I feel that the last solution might be the right one. I will try to understand it now and will come with some proposals soon on the forum.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [December 16, 2023, 4:10pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/5 "2023-12-16T16:10:29Z")

</div>

> I didn’t get what the db\_collation is to be honest. Is it automatic way to store values always in lower case in database?

Collations are a way to define properties such as the ordering and equality of set of textual data. How they are stored in usually backend specific. A backend might preserve the case at storage while allowing specialized ordering or comparison rules to be used.

> I feel that the last solution might be the right one. I will try to understand it now and will come with some proposals soon on the forum.

Both should work yes. Give a shot creating your own `Lookup` subclass, register it on your tag name and report issues you might encounter.

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 4:15pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/6 "2023-12-16T16:15:42Z")

</div>

> [@charettes](#):
>
> Give a shot creating your own `Lookup` subclass, register it on your tag name and report issues you might encounter.

I will. Thx 🙂

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 4:48pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/7 "2023-12-16T16:48:40Z")

</div>

Ok. I came with something like that based on search in google.

```auto
from django.db.models import Field
from django.db.models.lookups import In

@Field.register_lookup
class IIn(In):
    # Case-insensitive version of __in filter.
    lookup_name = 'iin'

    def process_lhs(self, *args, **kwargs):
        lhs, params = super().process_lhs(*args, **kwargs)

        lhs = 'LOWER({lhs})'
        return lhs, params

    def process_rhs(self, compiler, connection):
        rhs, params = super().process_rhs(compiler, connection)

        params = tuple(p.lower() for p in params)
        return rhs, params

```

I have placed the class in model.py file and changed the search from \_\_in to \_\_iin inside my ListView

```auto
class ItemSearchListView(ListView):
   model = Item

   def get_queryset(self):
       q = self.request.GET.get('q')
       search_list = q.split(',')
       return Item.objects.filter(Q(Tag_set __text__ iin=search_list)).distinct()

```

but something is wrong because it does not search at all unfortunately.  
For sure I did something wrong, because I am not saying I fully understand what’s going on in that code.  
I understand that Left-hand side (lhs) is taking database field and applying LOWER() function on it. Right-hand side (rhs) goes via parameters and makes them (strings) also lower(). So finally both sides (field and values) are made to be low-case and will be compared in such form.

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 5:01pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/8 "2023-12-16T17:01:00Z")

</div>

> [@charettes](#):
>
> register it on your tag name

Should I register the Lookup on Tag model or Tag.text field?

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [December 16, 2023, 5:17pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/9 "2023-12-16T17:17:41Z")

</div>

Lookups should be registered on the field you want to lookup against.

Either `Tag._meta.get_field('name').register_lookup(YourLookup)` so it’s only available to the `Tag.name` field or `TextField.register_lookup` if you want it to be available for all the `TextField`s in your project.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [December 16, 2023, 5:27pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/10 "2023-12-16T17:27:05Z")

</div>

I suggest you use breakpoints within your view and the lookup definition to debug this further.

Within your view you can do

```python
print(str(Item.objects.filter(Q(Tag_set __text__ iin=search_list)).query))

```

To see the generated SQL

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 5:33pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/11 "2023-12-16T17:33:51Z")

</div>

> [@charettes](#):
>
> TextField.register\_lookup

I have registered it as follows

```auto
@Field.register_lookup

```

Does it mean it will be available for all fields in all models?

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 5:42pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/12 "2023-12-16T17:42:09Z")

</div>

> [@charettes](#):
>
> `print(str(Item.objects.filter(Q(Tag_set __text__ iin=search_list)).query))`

Thank you so much for letting me know how to check the SQL query.  
I have run it and got the result:

```auto
 WHERE LOWER({lhs}) IN (test_text)

```

It means the lookup did not translate database field, but took {lhs} instead.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [December 16, 2023, 5:44pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/13 "2023-12-16T17:44:41Z")

</div>

I think you meant to use an `f` string in `process_lhs`; it should be `lhs = f'LOWER({lhs})'`

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 5:55pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/14 "2023-12-16T17:55:15Z")

</div>

You are right. I changed it to:

```auto
'LOWER({})'.format(lhs)

```

and now your command gave me correct result:

```auto
 WHERE LOWER("app_test_tag"."text") IN (test_text1, test_text2)

```

and ALL works as Swiss wristwatch 🙂 The solution is really elegant comparing to the loop over values and fields that builds strange query.

Thank you so much. Django is amazing!

---

<div class="post-metadata">

**Author:** ![Lexe-Pl](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lexe-pl/32/17723_2.png) [@Lexe-Pl](https://forum.djangoproject.com/u/Lexe-Pl)\
**Post date:** [December 16, 2023, 6:23pm UTC](https://forum.djangoproject.com/t/elegant-way-in-insensitive-case/26217/15 "2023-12-16T18:23:21Z")

</div>

I have just found something like Transform:

```auto
from django.db.models import Transform

class LowerCase(Transform):
    lookup_name = 'lower'
    function = 'LOWER'
    bilateral = True

```

that should generate SQL output as:

```auto
SELECT ... WHERE LOWER("tag"."text") = LOWER('test_text1')

```

It is even shorter and more elegant, am I right? Will it work?
