CODEWITHSUNDEEP
pythonmysqldjango
alstr
alstr

Reputation: 1556

Django filter icontains returns no results when string contains whitespace - mysql.connector.django

I have a search autocomplete box that returns courses when users enter a query string. This is working fine on my development server, but when deployed to live it only returns results when the user enters a single word and not multiple words separated by spaces. This is an issue as the course titles can contain spaces.

For reasons beyond my control, I am having to use mysql.connector.django as the database backend. I have limited access to the configuration of the database.

This is the code that works in development but not in live when querying the same database:

courses = (Course.objects.filter(module__open=True, module__term__academic_year__live=True)
           .filter(title__icontains=query)
           .distinct()
           .order_by('category__title', 'title'))

These are the database settings:

DATABASES = {
    'default': {
        'ENGINE': 'mysql.connector.django',

        ...

        'PORT': '3306',
        'OPTIONS': {
            'use_pure': True
        }
    }
}

use_pure is set due to this bug.

I am aware that MySQL queries do not factor in case by default, but I would have thought that using icontains would still return results.

Upvotes: 0

Views: 1080

Answers (2)

Sharad Bhandari
Sharad Bhandari

Reputation: 67

  • Change your Table collations to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  • Change your Database collation to utf8mb4_general_ci;

Now django would handle the rest. It helps to support unicode character and filter starts working using icontains filter.

Note : try in development server before updating in production directly.

Upvotes: 0

alstr
alstr

Reputation: 1556

The query string is passed as a URL parameter, and I found that when it contained a space, the view was trying to process the space as %20 instead, so obviously no results were returned.

I unquoted the query as follows:

from urllib.parse import unquote

...

query = unquote(query)

I am assuming this is an intricacy of the server. I've never had to do this with my other apps deployed elsewhere. If anyone can confirm however I would be interested.

Upvotes: 1

Related Questions