Django 3.1 SearchQuery now supports ‘websearch’ search type on PostgreSQL 11+ ¶
https://fediverse.org/simonw/status/1287155005095469056?s=20 ¶
SearchQuery description ¶
SearchQuery translates the terms the user provides into a search query object that the database compares to a search vector .
By default, all the words the user provides are passed through the stemming algorithms, and then it looks for matches for all of the resulting terms.
-
If search_type is ‘plain’ , which is the default, the terms are treated as separate keywords.
-
If search_type is ‘phrase’ , the terms are treated as a single phrase.
-
If search_type is ‘raw’ , then you can provide a formatted search query with terms and operators.
-
If search_type is ‘websearch’ , then you can provide a formatted search query, similar to the one used by web search engines .
‘websearch’ requires PostgreSQL ≥ 11. Read PostgreSQL’s Full Text Search docs to learn about differences and syntax.
Examples ¶
>>> from django.contrib.postgres.search import SearchQuery
>>> SearchQuery('red tomato') # two keywords
>>> SearchQuery('tomato red') # same results as above
>>> SearchQuery('red tomato', search_type='phrase') # a phrase
>>> SearchQuery('tomato red', search_type='phrase') # a different phrase
>>> SearchQuery("'tomato' & ('red' | 'green')", search_type='raw') # boolean operators
>>> SearchQuery("'tomato' ('red' OR 'green')", search_type='websearch') # websearch operators
SearchQuery terms can be combined logically to provide more flexibility:
>>> from django.contrib.postgres.search import SearchQuery
>>> SearchQuery('meat') & SearchQuery('cheese') # AND
>>> SearchQuery('meat') | SearchQuery('cheese') # OR
>>> ~SearchQuery('meat') # NOT
SearchQuery with search_query=”websearch” ¶
https://ep2020.europython.eu/media/conference/slides/iGXPHck-a-pythonic-full-text-search.pdf ¶
Django & Postgres Full Text Search ¶
Basic full text search support was added way back in Django 1.10.
The Django Postgres Full Text Search Docs do a pretty good job, you’ll see that it presents an interface that contains SearchQuery and SearchVector that can be used to interface with the underlying tsquery and tsvector.
If you want basic keyword search, or to handle custom search queries you craft in the ORM, that’ll take you pretty far.
But if you take a look at the example on that page, you’ll see that there’s no difference in results between SearchQuery(‘red tomato’) and SearchQuery(‘tomato red’).
Both evaluate to the same underlying tsquery object since to_tsquery interprets spaces as an ‘or’.
That’s that same tsquery issue we flagged before.
So if you want to search for phrases you can craft your own miniature parser that handles words like ‘and’ & ‘or’, or takes care of quoted strings and uses the search_type=’phrase’ option available since Django 2.2.
Of course, any time you see the sentence “you can craft your own miniature parser” you probably start to sweat a little bit.
There are a lot of edge cases, and you’re just trying to add search to your web application.
Enter websearch_to_tsquery ¶
As of Postgres 11, there is a new function available, websearch_to_tsquery.
The docs sum it up well:
websearch_to_tsquery creates a tsquery value from query text using an alternative syntax in which simple unformatted text is a valid query.
Unlike plainto_tsquery and phraseto_tsquery, it also recognizes certain operators.
Moreover, this function should never raise syntax errors, which makes it possible to use raw user-supplied input for search.
The following syntax is supported:
-
unquoted text: text not inside quote marks will be converted to terms separated by & operators, as if processed by plainto_tsquery.
-
“quoted text”: text inside quote marks will be converted to terms separated by <-> operators, as if processed by phraseto_tsquery. OR: logical or will be converted to the | operator.
-
-: the logical not operator, converted to the ! operator.
A pythonic full-text search by Paolo Melchiorre ( https://fediverse.org/pauloxnet ) ¶
See also
Annonce ¶
https://fediverse.org/pauloxnet/status/1286024244485382144?s=20 ¶
Introduction ¶
How to implement full-text search using only Django and PostgreSQL.
Keeping in mind the pythonic principle that “simple is better than complex” we will see how to implement full-text search in a web service using only Django and PostgreSQL and we will analyse the advantages of this solution compared to more complex solutions based on dedicated search engines.
Abstract ¶
A full-text search on a website is the best way to make its contents easily accessible to users because it returns better results and is i n fact used in online search engines or social networks.
The implementation of full-text search can be complex and many adopt the strategy of using dedicated search engines in addition to the database, but in most cases this strategy turns out to be a big problem of architecture and performance.
In this talk we’ll see a pythonic way to implement full-text search on a website using only Django and PostgreSQL, taking advantage of all the innovations introduced in latest years, and we’ll analyse the problems of using additional search engines with examples deriving from my experience (e.g. djangopoject.com or readthedocs.org).
Through this talk you can learn how to add a full-text search on your website, if it’s based on Django and PostgreSQL, or you can learn how to update the search function of your website if you use other search engines
The slides ¶
See also
https://ep2020.europython.eu/media/conference/slides/iGXPHck-a-pythonic-full-text-search.pdf ¶
https://ep2020.europython.eu/media/conference/slides/iGXPHck-a-pythonic-full-text-search.pdf ¶
PostgreSQL full-text search in the Django Admin by Simon Willison ¶
See also
Django 3.1 introduces PostgreSQL search_type=”websearch”, which gives you search with advanced operators like “phrase search” -excluding.
James Turk wrote about this here , and it’s also in my weeknotes .
I decided to add it to my Django Admin interface. It was really easy using the get_search_results() model admin method, documented here .
My models already have a search_document full-text search column, as described in Implementing faceted search with Django and PostgreSQL .
So all I needed to add to my ModelAdmin subclasses was this:
1 def get_search_results(self, request, queryset, search_term):
2 if not search_term:
3 return super().get_search_results(
4 request, queryset, search_term
5 )
6 query = SearchQuery(search_term, search_type="websearch")
7 rank = SearchRank(F("search_document"), query)
8 queryset = (
9 queryset
10 .annotate(rank=rank)
11 .filter(search_document=query)
12 .order_by("-rank")
13 )
14 return queryset, False
Here’s the full implementation for my personal blog.