Django - Full text search - Wildcard

Question

Is it possible to use wildcards in Django Full text search ?

https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/search/

post = request.POST.get('search')
query = SearchQuery(post)
vector = SearchVector('headline', weight='A') + SearchVector('content', weight='B')
rank = SearchRank(vector, query, weights=[0.1,0.2])
data = wiki_entry.objects.annotate(rank=SearchRank(vector,query)).filter(rank__gte=0.1).order_by('-rank')

At the moment it only matches on full words.

Characters like * % | & have no effect.

Or do i have to go back to icontains ?

https://docs.djangoproject.com/en/1.11/ref/models/querysets/#icontains

Any help is appreciated

Answer 1

I extend the django SearchQuery class and override plainto_tsquery with to_tsquery. Did some simple tests, it works. I will get back here if I find cases where this causes problems.

from django.contrib.postgres.search import SearchQuery

class MySearchQuery(SearchQuery):
    def as_sql(self, compiler, connection):
        params = [self.value]
        if self.config:
            config_sql, config_params = compiler.compile(self.config)
            template = 'to_tsquery({}::regconfig, %s)'.format(config_sql)
            params = config_params + [self.value]
        else:
            template = 'to_tsquery(%s)'
        if self.invert:
            template = '!!({})'.format(template)
        return template, params

Now I can do something like query = MySearchQuery('whatever:*')

Answer 2

[Postgres' part] The Postgres manual mentions this only briefly ( https://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES), but yes, it is possible, if you just need prefix matching:

test=# select to_tsvector('abcd') @@ to_tsquery('ab:*');
 ?column?
----------
 t
(1 row)


test=# select to_tsvector('abcd') @@ to_tsquery('ac:*');
 ?column?
----------
 f
(1 row)

And such query will utilize GIN index (I assume you have one).

[Django's part] I'm not Django user, so I made quick research and found that, unfortunately, Django uses plainto_tsquery() function, not to_tsquery(): https://docs.djangoproject.com/en/1.11/_modules/django/contrib/postgres/search/#SearchQuery

plainto_tsquery() made for simplicity, when you use just plain text as an input – so it doesn't support advanced queries:

test=# select to_tsvector('abcd') @@ plainto_tsquery('ab:*');
 ?column?
----------
 f
(1 row)

test=# select to_tsvector('abcd') @@ plainto_tsquery('ac:*');
 ?column?
----------
 f
(1 row)

So in this case, I'd recommend you using plain SQL with to_tsquery(). But you need to be sure you filtered out all special chars (like & or |) from your text input, otherwise to_tsquery() will produce wrong results or even error. Or if you can, extend django.contrib.postgres.search with the ability to work with to_tsquery() (this would be great contribution, btw).

Alternatives are:

• if your data is ACSII-only, you can use LIKE with prefix search and B-tree index created with text_pattern_ops / varchar_pattern_ops operator classes (if you need case-insensitivity, use functional index over lower(column_name) and lower(column_name) like '...%'; see https://www.postgresql.org/docs/9.6/static/indexes-opclass.html);
• use pg_trgm index, which supports regular expressions and GiST/GIN indexes (https://www.postgresql.org/docs/9.6/static/pgtrgm.html)