Using unaccent with SearchVector and SearchQuery in Django

Question

I have installed UnaccentExtension in Django but I'm having problems using it with this search:

vector = SearchVector('title__unaccent', 'abstract__unaccent')
query = SearchQuery(word) | SearchQuery(word2)
files = Doc.objects.annotate(rank=SearchRank(vector, query)).order_by('-rank')

This is the error:

Cannot resolve keyword 'unaccent' into field. Join on 'title' not permitted.

Whit a simplest search it works fine:

Doc.objects.filter(title__unaccent=word)

So, what am I doing wrong?

Preeminence

Answer 1

You can't use 'unaccent' in 'SearchVector' but you have to define a new "unaccented" config in PostgreSQL.

1. If you missed, installs the unaccent extension.

2. Create your unaccented dictionary in PostgrSQL or using an empty migrations with this SQL:

   CREATE TEXT SEARCH CONFIGURATION french_unaccent( COPY = french );
   ALTER TEXT SEARCH CONFIGURATION french_unaccent
   ALTER MAPPING FOR hword, hword_part, word
   WITH unaccent, french_stem;
   

3. Use this configuration in your Django query :

   SearchVector('title','abstract', config='french_unaccent')
   SearchQuery(word, config='french_unaccent')
   

You can find more info about this type of configuration in the official PostgreSQL documentation on in various articles

Methadone

creating that configuration I can do the query properly in Postgres with the SQL presented in the article but in Django I'm having the same issues. – Preeminence

I've various working site in production that use this solution. What do you mean for "same issues" ? – Methadone

in postgres im doing this: SELECT * FROM alumno WHERE to_tsvector('sp', name) @@ to_tsquery('sp', 'matias'); and from the database I have 1 match ** Matías Lermanda**. In the other hand if I do this in Django vector = SearchVector('name', conf='sp') query = SearchQuery('matias', conf='sp') a = Alumno.objects.annotate(search=vector).filter(search=query) I have 0 matches – Preeminence

So the issue is different from the error message in your question. You can try to view SQL executed from Django and compare it with your SQL or add here so we can analyze it. – Methadone

problem is solved, I was making the text search configuration with a postgres user different to the used in Django – Preeminence

Ok. So I hope you now can search correctly in Django, as your question? – Methadone

your answer was correct, but It wasnt working because of the problem with the users, so now all is working perfect. – Preeminence

I'm glad I could help you. Could you accept my answer? Thanks – Methadone

I tested this with the french dictionary and it still works like a charm with Postgresql 14 and Django 3.2! – Quadruped

@Quadruped thank for let us know that this solution still work with latest versions of PostgreSQL and Django – Methadone