Django trigram_similar

Question

I've been trying to implement trigram_similar functionality in my Django backend code. I get an error:

web_1         | Traceback (most recent call last):

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/exception.py", line 41, in inner

web_1         |     response = get_response(request)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/base.py", line 187, in _get_response

web_1         |     response = self.process_exception_by_middleware(e, request)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/base.py", line 185, in _get_response

web_1         |     response = wrapped_callback(request, *callback_args, **callback_kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/views/decorators/csrf.py", line 58, in wrapped_view

web_1         |     return view_func(*args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/views/generic/base.py", line 68, in view

web_1         |     return self.dispatch(request, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 489, in dispatch

web_1         |     response = self.handle_exception(exc)

web_1         |   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 449, in handle_exception

web_1         |     self.raise_uncaught_exception(exc)

web_1         |   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 486, in dispatch

web_1         |     response = handler(request, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/rest_framework/generics.py", line 201, in get

web_1         |     return self.list(request, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/rest_framework/mixins.py", line 40, in list

web_1         |     queryset = self.filter_queryset(self.get_queryset())

web_1         |   File "/usr/local/lib/python3.6/site-packages/profilehooks.py", line 239, in new_fn

web_1         |     return fp(*args, **kw)

web_1         |   File "/usr/local/lib/python3.6/site-packages/profilehooks.py", line 347, in __call__

web_1         |     return profiler.runcall(self.fn, *args, **kw)

web_1         |   File "/usr/local/lib/python3.6/cProfile.py", line 109, in runcall

web_1         |     return func(*args, **kw)

web_1         |   File "/code/src/users/views.py", line 266, in filter_queryset

web_1         |     major__name__trigram_similar = "CS"

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 784, in filter

web_1         |     return self._filter_or_exclude(False, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 802, in _filter_or_exclude

web_1         |     clone.query.add_q(Q(*args, **kwargs))

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1250, in add_q

web_1         |     clause, _ = self._add_q(q_object, self.used_aliases)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1276, in _add_q

web_1         |     allow_joins=allow_joins, split_subq=split_subq,

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1210, in build_filter

web_1         |     condition = self.build_lookup(lookups, col, value)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1102, in build_lookup

web_1         |     lhs = self.try_transform(lhs, name, lookups)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1120, in try_transform

web_1         |     (name, lhs.output_field.__class__.__name__))

web_1         | django.core.exceptions.FieldError: Unsupported lookup 'trigram_similar' for CharField or join on the field not permitted.

web_1         | 2018-11-11 19:03:58,144 ERROR Internal Server Error: /mentors/

web_1         | Traceback (most recent call last):

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/exception.py", line 41, in inner

web_1         |     response = get_response(request)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/base.py", line 187, in _get_response

web_1         |     response = self.process_exception_by_middleware(e, request)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/core/handlers/base.py", line 185, in _get_response

web_1         |     response = wrapped_callback(request, *callback_args, **callback_kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/views/decorators/csrf.py", line 58, in wrapped_view

web_1         |     return view_func(*args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/django/views/generic/base.py", line 68, in view

web_1         |     return self.dispatch(request, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-packages/rest_framework/views.py", line 489, in dispatch

web_1         |     response = self.handle_exception(exc)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/rest_framework/views.py", line 449, in handle_exception

web_1         |     self.raise_uncaught_exception(exc)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/rest_framework/views.py", line 486, in dispatch

web_1         |     response = handler(request, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/rest_framework/generics.py", line 201, in get

web_1         |     return self.list(request, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/rest_framework/mixins.py", line 40, in list

web_1         |     queryset = self.filter_queryset(self.get_queryset())

web_1         |   File "/usr/local/lib/python3.6/site-
packages/profilehooks.py", line 239, in new_fn

web_1         |     return fp(*args, **kw)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/profilehooks.py", line 347, in __call__

web_1         |     return profiler.runcall(self.fn, *args, **kw)

web_1         |   File "/usr/local/lib/python3.6/cProfile.py", line 109, 
in runcall

web_1         |     return func(*args, **kw)

web_1         |   File "/code/src/users/views.py", line 266, in 
filter_queryset

web_1         |     major__name__trigram_similar = "CS"

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/query.py", line 784, in filter

web_1         |     return self._filter_or_exclude(False, *args, **kwargs)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/query.py", line 802, in _filter_or_exclude

web_1         |     clone.query.add_q(Q(*args, **kwargs))

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 1250, in add_q

web_1         |     clause, _ = self._add_q(q_object, self.used_aliases)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 1276, in _add_q

web_1         |     allow_joins=allow_joins, split_subq=split_subq,

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 1210, in build_filter

web_1         |     condition = self.build_lookup(lookups, col, value)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 1102, in build_lookup

web_1         |     lhs = self.try_transform(lhs, name, lookups)

web_1         |   File "/usr/local/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 1120, in try_transform

web_1         |     (name, lhs.output_field.__class__.__name__))

web_1         | django.core.exceptions.FieldError: Unsupported lookup 'trigram_similar' for CharField or join on the field not permitted.

I've followed the instructions on this page https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/lookups/ but to no avail. I have properly installed the dependencies and added the extra files.

     for item in query:
                     item_alias = trans_dict.get(item.lower(),item)
                     major_alias = major_dict.get(item.lower(),item)
                     queryset = queryset.filter(
                         major__name__trigram_similar = major_alias
                     )

Answer 1

The trigram_similar lookup allows you to perform trigram lookups, measuring the number of trigrams (three consecutive characters) shared, using a dedicated PostgreSQL extension. A trigram lookup is given an expression and returns results that have a similarity measurement greater than the current similarity threshold.

To use it, add 'django.contrib.postgres' in your INSTALLED_APPS and activate the pg_trgm extension on PostgreSQL. You can install the extension using the TrigramExtension migration operation.

You need to create a new blank migration, for the app.model in question with the following:

from django.contrib.postgres.operations import TrigramExtension

class Migration(migrations.Migration):
    operations = [
        TrigramExtension(),
    ]

This all has to be done manually (be sure to keep dependencies in there).

python manage.py makemigrations <insert_app_name_here> --empty 

**N.B. It's worth noting that your database backend needs to PostgreSQL...