django: how to filter model field values with out space?

Question

I have asked this question before. May be due to lack of desired answers, again, I take this as a opportunity to explain my problem in a nice fashion in this space.

Please note: I am using MySQL database

I have a model called City. It contains id, name.

id      name
1       New York
2       New India
3       New USA
4       New UK

Now by using the below syntax

Please note: As a sample example I put static value i.e. New in the below syntax. At the end it should be dynamic value.

City.objects.filter(name__icontains="New")

Obviously I get New York, New India, New USA,New UK

From this point I am good.

---

Now moving to the my problem.I want to filter only New York city name. The user could type newyork (or) New York (or) NEWYORK (or) NEw yOrk (or) some other way, but at the end I have to strip the spaces from the model field value and get desired results.

City.objects.filter(name__iregex= "newyork")  ?// How to fix this one

CURRENT OUTPUT is:
[]

DESIRED OUTPUT is:
New York

Answer 1

You can use a custom lookup function for this.

from django.db.models import Transform

class SpaceRemovedValue(Transform):
    lookup_name = 'nospaces'

    def as_sql(self, compiler, connection):
        lhs, params = compiler.compile(self.lhs)
        return "REPLACE(%s, ' ', '')" % lhs, params

from django.db.models import CharField
CharField.register_lookup(SpaceRemovedValue)

Now you can use it:

City.objects.filter(name__nospaces= "newyork")

Note this is pretty inefficient. It requires MySQL to scan and process every row in the City table; if you have more than a few, it will take a while.

An alternative approach would be to save a spaces-removed version of the name field into a separate field, which you could then query directly. You could override the save method to automatically populate the field before saving it.

Answer 2

A possibility is to add an optional whitespace character \s* in between each one of the search string characters:

def insert_whitespace(string):
    s = []
    for i in range(0, len(string)):
        s.append(string[i:i+1])
    return '\\s*'.join(s)

Then

City.objects.filter(name__iregex= insert_whitespace("newyork"))

Answer 3

I'm sure this is not the best solution, but it may help.

# Query you entire table
from myapp.models import City

cities = City.objects.all().values('name', 'id')  # If it get extra values later
user_entry = 'newyork'

def is_match(cities, user_entry):
    result_list = []
    for city in cities:
        for value in city['name'].lower().split(' '):
            if value in user_entry.lower():
                l.append(city['id'])
                break
    return result_list

# Then call
is_match(cities=cities, user_entry=user_entry)

# Should return something like this
[1L]  # A list with id of possibles matchs

The function is_match could be a list comprehension but in this way is more legible I think.

This will be more inefficient with bigger data, but may could give you an idea to start.