Django/Postgres fulltext search - exclude if value contains <STRING>-CodePudding

Trying to use Postgres search in Django using SearchVector and I want to exclude results if contain a given string. Is it possible?

Example from docs:

Entry.objects.annotate(search=SearchVector('body_text', 'blog__tagline')).filter(search='Cheese')

What if I want to exclude objects whose blog__tagline contains "queso"?

I can't exclude objects with "queso" before annotation because I want to include them when a search is not performed.

CodePudding user response：

Entry.objects.exclude(blog__tagline__icontains='queso').annotate(search=SearchVector('body_text', 'blog__tagline')).filter(search='Cheese')

if you need a quick fix, i'd recommend exclude.

CodePudding user response：

I can suggest two solutions: First, you can apply logical operators on your SearchQuery objects.

cheese = SearchQuery('cheese')
not_cheese = ~SearchQuery('cheese')
cheese_and_not_queso = SearchQuery('cheese') & ~SearchQuery('queso')
...
Entry.objects.annotate(search=SearchVector('body_text', 'blog__tagline')).filter(search=cheese_and_not_queso)

Second, you can use search operators in your query, but you need some additional configurations. filter(search='Cheese') it creates a SearchQuery is equivalent to filter(search=SearchQuery('Cheese', search_type='plain')). In plain search type (see Postgres doc), search operators and special full-text search characters are stripped and we don't want that. search_type accepts four modes: 'plain', 'phrase', 'raw' and 'websearch'. If you use 'raw' as search_type, you can use these operators in you query string:

& (AND) 
| (OR)
! (NOT)
<-> (FOLLOWED BY)

So you can change your query to SearchQuery("cheese & !queso", search_type='raw'). What about other search types? 'phrase' is like 'plain' in stripping behavior but 'websearch' able us to use web search engine syntax (like Google) in our query. So query can be rephrased in SearchQuery("cheese -queso", search_type='websearch')

Django doc on full-text search and Postgres and Postgres doc on controlling searches are best places to learn more.