Elegant way __in insensitive case

[Lexe-Pl]

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

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

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.

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

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.

[Lexe-Pl]

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.

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

[charettes]

Hello 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 or convert the column at run time. 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 might make the solution more elegant.

[Lexe-Pl]

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.

[charettes]

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.

[Lexe-Pl]

@charettes

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

I will. Thx

[Lexe-Pl]

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

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

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.

[Lexe-Pl]

@charettes

register it on your tag name

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

[charettes]

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 TextFields in your project.

[charettes]

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

Within your view you can do

print(str(Item.objects.filter(Q(Tag_set__text__iin=search_list)).query))

To see the generated SQL

[Lexe-Pl]

@charettes

TextField.register_lookup

I have registered it as follows

@Field.register_lookup

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

[Lexe-Pl]

@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:

 WHERE LOWER({lhs}) IN (test_text)

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

[charettes]

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

[Lexe-Pl]

You are right. I changed it to:

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

and now your command gave me correct result:

 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!

[Lexe-Pl]

I have just found something like Transform:

from django.db.models import Transform

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

that should generate SQL output as:

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

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