Django Query with Case Insensitive Data Both Ways

Question

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:

foo is in User.name
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:

willeM_ Van Onsem · Accepted Answer · 2020-06-05 20:36:17Z

1

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'))
)

edited Jun 5, 2020 at 20:36

answered Jun 5, 2020 at 15:38

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nbixler Over a year ago

I'm having some trouble with implementation (and my apologies if there's more standard SO follow up protocol). Do I need to be using foo=Value(foo, CharField)?

willeM_ Van Onsem Over a year ago

@nbixler: sorry, Value(foo, CharField()) (so a CharField *object).

Collectives™ on Stack Overflow

Django Query with Case Insensitive Data Both Ways

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related