`TrigramSimiliarity` on `ArrayField`

Using Django Using the ORM
1

Hi there Guys,

I have a small question regarding django’s full text search

Lets take a look at the following example

# models.py
class AnimeModel(models.Model):
   name_synonyms = ArrayField(
        # https://stackoverflow.com/questions/61206968/setting-arrayfield-to-null-or
        default=list,
        blank=True,
        null=False,
        base_field=models.CharField(max_length=1024),
    )

# views.py
name = "Naruto"
query = (
    AnimeModel.objects
        .annotate(
            similiarity=TrigramSimilarity(
                "name_synonyms", name
            )
        )
        .filter(
            similiarity__gte=0.1,
        )
        .order_by("-similiarity")
    )

Now i am getting this error

django.db.utils.ProgrammingError: function similarity(character varying[], unknown) does not exist
LINE 1: ...l". SIMILARITY...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Does anyone know how to do fuzzy index lookup on ArrayField?

Django version : 4.2a1
Psycopg Version : 2.9.5

Thanks :smiley:
Heres my postgres output

Server [localhost]:
Database [postgres]: django
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (15.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

django=#
django=# \dx
                                     List of installed extensions
   Name    | Version |   Schema   |                            Description
-----------+---------+------------+-------------------------------------------------------------------
 btree_gin | 1.3     | public     | support for indexing common datatypes in GIN
 hstore    | 1.8     | public     | data type for storing sets of (key, value) pairs
 pg_trgm   | 1.6     | public     | text similarity measurement and index searching based on trigrams
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 unaccent  | 1.1     | public     | text search dictionary that removes accents
(5 rows)


django=# CREATE EXTENSION pg_trgm;
ERROR:  extension "pg_trgm" already exists

Is this feature not available on postgres or is it something more django specific

Stacktrace :

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\ninja\operation.py", line 99, in run
    result = self.view_func(request, **values)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\ninja\pagination.py", line 143, in view_with_pagination 
    items = func(*args, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\Desktop\CoreProject\backend\django_core\apps\api\views\anime\__init__.py", line 91, in get_anime_info
    if special_query:
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\query.py", line 412, in __bool__       
    self._fetch_all()
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\query.py", line 1881, in _fetch_all    
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\query.py", line 91, in __iter__        
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\sql\compiler.py", line 1527, in execute_sql
    cursor.execute(sql, params)
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\debug_toolbar\panels\sql\tracking.py", line 230, in execute
    return self._record(self.cursor.execute, sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\debug_toolbar\panels\sql\tracking.py", line 154, in _record
    return method(sql, params)
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 103, in execute      
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 67, in execute       
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 84, in _execute      
    with self.db.wrap_database_errors:
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 89, in _execute      
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: function similarity(character varying[], unknown) does not exist
LINE 1: ...l"."theme_endings", "anime_animemodel"."updated", SIMILARITY...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2

What is the “name” you are referencing here? Where does that come from?

3

Hi there, thanks for responding.

So this full model is available at CoreProject/__init__.py at d60de2e79000fef1d119fb997eb5984faa3f11c3 · baseplate-admin/CoreProject · GitHub

I just gave a POC that can reproduce the error i am facing

4

Ok, so name is a field in the model.

When you are referencing a field within an expression in a query, you need to reference it using the F function.

5

Okay so if i modify my code like this

query = (
            AnimeModel.objects
            .annotate(
                similiarity=TrigramSimilarity(
                    F("name_synonyms"),
                    name,
                )
            )
            .filter(
                similiarity__gte=0.1,
            )
            .order_by("-similiarity")
        )

It still gives me the same error …

Traceback (most recent call last):
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedFunction: function similarity(character varying[], unknown) does not exist
LINE 1: ...l"."theme_endings", "anime_animemodel"."updated", SIMILARITY...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\ninja\operation.py", line 99, in run
    result = self.view_func(request, **values)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\ninja\pagination.py", line 143, in view_with_pagination
    items = func(*args, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\Desktop\CoreProject\backend\django_core\apps\api\views\anime\__init__.py", line 95, in get_anime_info
    if special_query:
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\query.py", line 412, in __bool__
    self._fetch_all()
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\query.py", line 91, in __iter__
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\models\sql\compiler.py", line 1527, in execute_sql
    cursor.execute(sql, params)
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\debug_toolbar\panels\sql\tracking.py", line 230, in execute
    return self._record(self.cursor.execute, sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\debug_toolbar\panels\sql\tracking.py", line 154, in _record
    return method(sql, params)
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 103, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\Asus\AppData\Local\pypoetry\Cache\virtualenvs\coreproject-jIQ747rN-py3.11\Lib\site-packages\django\db\backends\utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: function similarity(character varying[], unknown) does not exist
LINE 1: ...l"."theme_endings", "anime_animemodel"."updated", SIMILARITY...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
6

Which identifier did I say you needed to change? (You changed the wrong reference - or, you need to change both - I’m not sure.)

7

I am truly sorry that i didn’t understand your previous response… I thought you meant name as in model. I used name as another variable in my views.py code…

        name = 'naruto'
        query = (
            AnimeModel.objects
            .annotate(
                similiarity=TrigramSimilarity(
                    "name_synonyms",
                    name,
                )
            )
            .filter(
                similiarity__gte=0.1,
            )
            .order_by("-similiarity")
        )

I have refactored my original response to clarify my question

8

Ok, which is why I asked where name came from in that expression, and then you directed me to your model definition showing name as a field.

Anyway, from what I can see, the first parameter to TrigramSimilarity must be an “atomic” field and not an array. You’re right that the field name looks like it should be a string and not a field reference, but I don’t see anything in the docs that allow it to be used with an array field.

See PostgreSQL: Documentation: 15: F.35. pg_trgm

9

You are right on this part. “Array” is not “atomic”

From the docs

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Tho i wish it was possible

Never the less thank you for your time and have a good day

10

I thought I could bypass this by constructing a subquery that is first a combined string aggregate of the ArrayField, is this not a valid strategy either? Have tried a few Raw SQL and more pythonic ORM attempts at this and returned similar errors where either SIMILARITY or the STRING_AGG itself do not exist. Thoughts appreciated!

11

I think this is probably a limitation of postgres itself.