CODEWITHSUNDEEP
djangodjango-querysetcase-insensitive
nbixler
nbixler

Reputation: 522

Django Query with Case Insensitive Data Both Ways

There are a lot of similar questions, but I'm only finding partial solutions. I have a group of users stored as objects, with a name attribute (User.name). I'm hoping to do a query with a user input (Foo) such that I can (without being case sensitive) find all users where either:

  1. foo is in User.name
  2. User.name is in foo

As an example, I want the user to be able to type in "Jeff William II" and return "Anderson Jeff William II", "jeff william iii", as well as "Jeff Will" and "william ii"

I know I can use the Q function to combine two queries, and I can use annotate() to transform User.name like so (though I welcome edits if you notice errors in this code):

users = User.objects.annotate(name_upper=Upper(name)).filter(Q(name_upper__icontains=foo) | Q(name_upper__in=foo))

But I'm running into trouble using __in to match multiple letters within a string. So if User.name is "F" I get a hit when inputting Jeff but if User.name is "JE" then it doesn't show up. How do I match multiple letters, or is there a better way to make this query?

SIDE NOTE: I initially solved this with the following, but would prefer making a query if possible.

for u in User.objects.all():
     if u.name in foo or foo in u.name:

Upvotes: 1

Views: 1755

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476574

Please do not use Upper. It is a common misconception that by converting two items to uppercase (or lowercase) you make a case insenstive equality check. Certain characters, like ß have no uppercase/lowercase, and have more complicated rules (collation) to consider these equivalent. In Python one uses .casefold(…) [python-doc] for that.

For the database, you can simply make use of annotate, and then use two checks:

from django.db.models import CharField, F, Q, Value

foo = 'Jeff William II'

User.objects.annotate(foo=Value(foo, CharField())).filter(
    Q(name__icontains=foo) | Q(foo__icontains=F('name'))
)

Upvotes: 1

Related Questions