0

My problem is that for one of my models, the search bar from the corresponding admin page is performing a case sensitive search when it should be a case insensitive search by default.

From what I could find here, I feel like this might have something to do with database encoding but from the doc, it says that Django assumes that everything is UTF-8 encoded and I checked my MySQL table and the fields are encoded in UTF-8.

Could it be due to the presence of Greek characters in one of my field? How can I force a case insensitive search in at least the fields that don't contain Greek letters? Currently, the search is case sensitive for all the fields. But it works correctly (case insensitive) for other models.

Model:

class MyModel(models.Model):
    id = models.AutoField(primary_key=True)
    field1 = models.CharField(max_length=200) # Might contain greek letters
    field2 = models.CharField(max_length=200, unique=True)

Not sharing the admin class code as nothing fancy is in here. Just standard combination of list_display, search_fields, ordering and fieldsets.

Using Django 1.10

EDIT
Here is the output of SHOW CREATE TABLE for the table in question:

CREATE TABLE `my_model` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `field1` varchar(200) COLLATE utf8_bin NOT NULL,
    `field2` varchar(200) COLLATE utf8_bin NOT NULL,
    # Non relevant other fields
    PRIMARY KEY (`id`),
    UNIQUE KEY `my_model_field2_63f9df5e_uniq` (`field2`)
) ENGINE=InnoDB AUTO_INCREMENT=657 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Now that I see that, it's probably due to _bin right? Should I convert to something with _ci? What's the way of doing that in Django?

Cyrlop
  • 1,894
  • 1
  • 17
  • 31

2 Answers2

1

utf8_bin is case sensitive. Change to utf8_unicode_520_ci (if using MySQL 5.6 or 5.7):

ALTER TABLE my_model CONVERT TO COLLATION utf8_unicode_520_ci;

Using LOWER() may work, but it is slow because it prevents using the index, thereby potentially making queries run a lot slower than they could.

Note: To allow for Emoji and all of Chinese, utf8mb4 is needed in place of utf8.

Your model specifies 3 unique keys for a single table? That seems excessive. Then I am confused by the CREATE TABLE -- it shows only 2. (Note: PRIMARY KEY is UNIQUE.)

According to Django docs "In all cases, you set the collation by directly manipulating the database tables; Django doesn’t provide a way to set this on the model definition." As demonstrated by the OP, using SHOW CREATE TABLE via MySQL utilities can tell you what collation is currently being used.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks I understand now that if I use `utf8_whatever_ci` it will probably solve my issue. I was wondering if there was a Django way of doing that? – Cyrlop Aug 24 '18 at 20:28
  • Could you edit your answer to specify that "Django doesn't support any way for collation manipulation through ORM." (that's the other 50% of answer I was looking for) so I can accept your answer? [See this part of the doc](https://docs.djangoproject.com/en/dev/ref/databases/#collation-settings). Also the field1 wasn't unique, I edited my question. – Cyrlop Aug 25 '18 at 09:21
  • @Cyrlop - Yeah, that doc has a very specific note on collation. I revamped much of the answer. – Rick James Aug 25 '18 at 14:51
0
from django.db.models.functions import Lower
qs = MyModel.objects.annotate(field1_lower=Lower('field1')) # annotate each item in the queryset with the code_lower

Now search with field1_lower instead of field1. Hope that helps.

hd1
  • 33,938
  • 5
  • 80
  • 91