0

I would like to make an accent-insensitive lookup in a french database (with words containing accents):

>>> User.objects.filter(first_name="Jeremy")

['<User: Jéremy>', '<User: Jérémy>', '<User: Jeremy>']

After lots of research, I found that Django has an Unaccent lookup for PostgreSQL but nothing for other databases (like MariaDB)

Is there a way to make this happen without changing the database to PostgreSQL?

Nour SIDAOUI
  • 164
  • 5
  • 1
    Could this be solved by changing the column's collation? http://sqlfiddle.com/#!9/e6da44/2 – Martin Burch Jan 06 '21 at 09:28
  • @MartinBurch I'm not very familiar with collation but found this thread: https://stackoverflow.com/questions/49316327/how-to-set-collation-in-mysql-database-with-django-2-mysqlclient if I got this right, by making this Alteration, I will have additional columns that contain the non-accentuated version of each cell? – Nour SIDAOUI Jan 06 '21 at 10:03
  • 1
    Yeah, I saw that thread too, but I can't really recommend it. Basically, if you can alter your MySQL/MariaDB column's collation _once_, _in the database_ (might want to make a backup first) then _every_ query (not just the ones made in Django) will disregard the accents (and even upper/lower case, that's what _ci means). This won't change the database column contents at all, just how the comparison (`WHERE first_name = 'jeremy'`) works. – Martin Burch Jan 06 '21 at 10:22

1 Answers1

1

Finally got it solved:

So first and foremost: get acquainted with SQL Collations! (Thanks to @MartinBurch) Collations set the searching rules so that SQL can match your given keywords.

So after lots of head-smashes, here is how I solved it:

1- head to your "my.cnf" file (if you know where is your my.cnf, you can skip to step 5)

2- Open your terminal and type:

$ which mysqld

That should return "/usr/sbin/mysqld" or another path depending on your configuration.

3- Use the path returned in step 2 as follows:

$ /your/path/to/mysqld --verbose --help | grep -A 1 "Default options"

That should return something that finishes like:

Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

4- Find the file that has a [mysqld] section. if none of them has one, just add it. Note: writing the section's name is very important SQL won't start otherwise

5- Add the following lines at the end of the file and Save:

[mysqld]
character_set_server = latin1
collation-server =  latin1_general_ci

6- Restart your MariaDB server (if you have a brew version):

brew services stop mariadb
brew services start mariadb

7- head to your Django project and use '__icontains' to make case insensitive and accent insensitive queries:

User.objects.filter(name__icontains=jeremy)
>>> ['<User: Jéremy>', '<User: Jérémy>', '<User: Jeremy>']
Nour SIDAOUI
  • 164
  • 5