def autocompleteModel(request):
if request.is_ajax():
q = request.GET.get('term', '').capitalize()
# search_qs = Post.objects.filter(title=q)
search_qs = Post.published.annotate(similarity=TrigramSimilarity(
'title', q), ).filter(similarity__gt=0.1).order_by('-similarity')
results = []
print(q)
for r in search_qs:
results.append(r.FIELD)
data = json.dumps(results)
else:
data = 'fail'
mimetype = 'application/json'
return HttpResponse(data, mimetype)
def post_search(request):
form = SearchForm()
query = None
results = []
if 'query' in request.GET:
form = SearchForm(request.GET)
if form.is_valid():
query = form.cleaned_data['query']
results = Post.published.annotate(similarity=TrigramSimilarity(
'title',
query), ).filter(similarity__gt=0.1).order_by('-similarity')
return render(request, 'blog/post/search.html', {
'form': form,
'query': query,
'results': results
})
def get_queryset(self):
if self.action != "list":
return super(PatientSearchViewSet, self).get_queryset()
else:
serializer = PatientSearchSerializer(
data=self.request.query_params, partial=True)
serializer.is_valid(raise_exception=True)
if self.request.user.user_type >= User.TYPE_VALUE_MAP[
"DistrictLabAdmin"]:
search_keys = [
"date_of_birth", "year_of_birth", "phone_number", "name",
"age"
]
else:
search_keys = [
"date_of_birth", "year_of_birth", "phone_number", "age"
]
search_fields = {
key: serializer.validated_data[key]
for key in search_keys if serializer.validated_data.get(key)
}
if not search_fields:
raise serializers.ValidationError({
"detail": [
f"None of the search keys provided. Available: {', '.join(search_keys)}"
]
})
if not self.request.user.is_superuser:
search_fields["state_id"] = self.request.user.state_id
if "age" in search_fields:
age = search_fields.pop("age")
year_of_birth = datetime.datetime.now().year - age
search_fields["age__gte"] = year_of_birth - 5
search_fields["age__lte"] = year_of_birth + 5
name = search_fields.pop("name", None)
queryset = self.queryset.filter(**search_fields)
if name:
queryset = (queryset.annotate(
similarity=TrigramSimilarity("name", name)).filter(
similarity__gt=0.2).order_by("-similarity"))
return queryset
def get(self, request, search_term):
books = models.Book.objects.annotate(
similarity=TrigramSimilarity('title1', search_term) +
TrigramSimilarity('title2', search_term) +
TrigramSimilarity('author', search_term) +
TrigramSimilarity('isbn', search_term) +
TrigramSimilarity('designation', search_term) +
TrigramSimilarity('subject', search_term), ).filter(
similarity__gte=0.1).order_by('-similarity')
books_serialized = serializers.Book(books, many=True)
return Response(books_serialized.data)
def trisearch(query, weight):
search = SearchQuery.objects.create(query=query)
results = BlockQuote.objects.annotate(
similarity=TrigramSimilarity('text', query)).filter(
similarity__gt=weight)
for result in results:
QueryParams.objects.create(
quote=result,
query=search,
)
search.finished = True
search.completed = datetime.now()
search.save()
def post_search(request):
form = SearchForm()
query = None
results = []
# check for submitted form
if "query" in request.GET:
form = SearchForm(request.GET)
if form.is_valid():
query = form.cleaned_data["query"]
results = (Post.published.annotate(similarity=TrigramSimilarity(
"title",
query), ).filter(similarity__gt=0.1).order_by("-similarity"))
return render(request, "blog/search.html", {
"form": form,
"query": query,
"results": results
})
def post_search(request):
form = SearchForm()
query = None
results = []
if 'query' in request.GET:
form = SearchForm(request.GET)
if form.is_valid():
query = form.cleaned_data['query']
# search_vector = SearchVector('title', weight='A') + SearchVector(
# 'body', weight='B')
# search_query = SearchQuery(query)
results = Post.objects.annotate(similarity=TrigramSimilarity('title', query),
).filter(similarity__gt=0.3).order_by('-similarity')
return render(request, 'blog/post/search.html',
{'form': form,
'query': query,
'results': results})
def list(self, request, **kwargs):
"""
Trigram based search on usernames by query passed in the url
Returns list of ordered user by the largest similarity
HTTP_204 if no results
"""
search_query = kwargs["query"]
if search_query:
results = User.objects.annotate(
similarity=TrigramSimilarity('username', search_query)
).filter(similarity__gt=0.1).order_by('-similarity')
if len(results):
page = self.paginate_queryset(results)
serializer = self.get_serializer(
page, many=True, context={'request': request})
return self.get_paginated_response(serializer.data)
return Response({"STATUS": "Can't find any users."}, status=status.HTTP_204_NO_CONTENT)
def get_card_image(request):
name_param = request.GET.get('name', None)
if not name_param or len(name_param) < 2:
raise APIException(code=400, detail="Parameter 'name' must be specified with a length greater than 2.")
name_param = name_param.lower()
cards = Card.objects \
.annotate(similarity=TrigramSimilarity('alias', name_param)) \
.filter(similarity__gt=0.20) \
.order_by('-similarity')
if not cards:
raise APIException(code=404, detail="No cards found")
img_link = cards[0].cardset_set.exclude(image='').order_by('-id')[0].image
return redirect(img_link)
def get(self, request, *args, **kwargs):
data = self.request.GET.get('search', None)
if data != None:
#jts = Jobtitles.objects.filter(jobtitle__icontains=data).distinct('jobtitle')[:10]
jts = Jobtitles.objects.annotate(similarity=TrigramSimilarity(
'jobtitle',
data), ).filter(similarity__gt=0.15).distinct('jobtitle')[:10]
jtresult = []
for jt in jts:
jtresult.append({'id': jt.id, 'jobtitle': jt.jobtitle})
return JsonResponse(jtresult, safe=False)
def cities(request):
context = dict()
try:
user = Account.objects.get(pk=request.session['user'])
context['user'] = user
except:
return redirect(reverse(accountsviews.index))
else:
pass
if request.method == 'POST':
searching_city = City.objects.annotate(similarity=TrigramSimilarity(
'name', request.POST['search']), ).filter(
similarity__gt=0.6).order_by('-similarity')
context['cities'] = searching_city
return render(request, 'app/city/cities1.html', context)
context['cities'] = City.objects.all().order_by('name')
return render(request, 'app/city/cities1.html', context)
def post_search(request):
form = SearchForm()
query = None
results = []
# search query
if 'query' in request.GET:
form = SearchForm(request.GET)
if form.is_valid():
query = form.cleaned_data['query']
results = Post.objects.annotate(
similarity=TrigramSimilarity('title', query)).filter(
similarity__gt=0.3).order_by('-similarity')
context = {'form': form, 'query': query, 'results': results}
return render(request, 'blog/post/search.html', context)
def search(phrase):
"""Return matching products for storefront views.
Fuzzy storefront search that is resistant to small typing errors made
by user. Name is matched using trigram similarity, description uses
standard postgres full text search.
Args:
phrase (str): searched phrase
"""
name_sim = TrigramSimilarity("name", phrase)
ft_in_description = Q(description__search=phrase)
ft_by_sku = Q(variants__sku__search=phrase)
name_similar = Q(name_sim__gt=0.2)
return Product.objects.annotate(name_sim=name_sim).filter(
(ft_in_description | name_similar | ft_by_sku))
def searchmovie(request):
name = request.GET.get('name')
if (name != None):
# res=Movies.objects.annotate(
# search=SearchVector('title'),
# ).filter(search=name)
res = Movies.objects.annotate(similarity=TrigramSimilarity(
'title',
name), ).filter(similarity__gt=0.01).order_by('-similarity')
print(res)
if (res):
context = {"name": name, "hasMovies": True, "movies": res}
print(context)
return render(request, "movies/search_movie.html", context)
context = {"hasMovies": False}
return render(request, "movies/search_movie.html", context)
def search(self, search_text):
search_vectors = (
SearchVector('title', weight='A', config='english') +
SearchVector('overview', weight='B', config='english') +
SearchVector(
StringAgg('content', delimiter=' '),
weight='C',
config='english',
))
search_query = SearchQuery(search_text, config='english')
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity('title', search_text)
return self.filter(search_vector=search_query)\
.prefetch_related('tags')\
.prefetch_related('bookmarked')\
.annotate(rank=search_rank + trigram_similarity)\
.order_by('-rank')
def get_queryset(self): # new
query = self.request.GET.get('search_query')
print(query)
search_query = SearchQuery(query)
search_vector = SearchVector('title', 'body', 'address')
search_rank = SearchRank(search_vector, search_query)
print(search_rank)
trigram_similarity = TrigramSimilarity('title', query)
object_list = House.objects.filter(archive=False).annotate(
search=search_vector
).filter(
search=search_query
).annotate(
rank=search_rank + trigram_similarity
).order_by('-rank')
return object_list
def filter_for_tokens(self, tokens: typing.Sequence[Token]):
trig_query = " ".join([t.value for t in tokens if t.type == TokenType.WORD])
if trig_query:
# We mix similarity and word_similarity to achieve better results in long strings
# See https://dev.to/moritzrieger/build-a-fuzzy-search-with-postgresql-2029
similarity = Greatest(
TrigramSimilarity("search", trig_query),
TrigramWordSimilarity("search", trig_query),
)
# Here, we do 2 things:
# First, we filter with __trigram_similar, this generates SQL like `WHERE search % 'the query'`
# where % is the similarity operator (https://www.postgresql.org/docs/current/pgtrgm.html#PGTRGM-OP-TABLE)
# This operator checks that the similarity is greater than pg_trgm.similarity_threshold
# We use the operator and not the function as postgresl does not hit the index for the function (sadly)
# (note: the GIN and GIST indexes are defined directly on the Index model)
#
# Then, we annotate with TrigramSimilarity("search", query) and it generates this SQL:
# `SELECT similarity("search", 'the query') as rank` and this is used to display the rank in the search.
results = (
self.filter(
Q(search__trigram_similar=trig_query)
| Q(search__trigram_word_similar=trig_query)
)
# exclude everything called 's3keep', it's noise from s3content manager
# TODO: don't index these files
.annotate(rank=similarity).order_by("-rank")
)
# pg_trgm.similarity_threshold is by default = 0.3 and this is too low for us.
# We increase it here.
# Warning: the SET is done right now but the queryset execution is lazy so could be delayed a lot
# there is no guarantee that someone will not SET a different value in the meantime
# (but probability is low)
with connection.cursor() as cursor:
cursor.execute("SET pg_trgm.similarity_threshold = %s", [0.1])
else:
results = self.annotate(rank=Value(0.5))
# filter with exact word
for t in tokens:
if t.type == TokenType.EXACT_WORD:
results = results.filter(search__contains=t.value)
return results
def post_search(request):
form = SearchForm()
query = None
results = []
if 'query' in request.GET:
form = SearchForm(request.GET)
if form.is_valid():
search_vector = SearchVector('title', weight='A') + SearchVector(
'body', weight='B')
search_query = SearchQuery(query)
results = Post.published.annotate(
similarity=TrigramSimilarity('title', query)).filter(
similarity=0.1).order_by('-similarity')
return render(request, 'blog/post/search.html', {
'form': form,
'query': query,
'results': results
})
def search(self, search_text):
search_vectors = (SearchVector('title', weight='A', config='english') +
SearchVector(
StringAgg('content', delimiter=' '),
weight='B',
config='english',
))
search_query = SearchQuery(
search_text,
config='english',
)
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity('title', search_text)
return (self.filter(
search_vector=search_query,
is_active=True).select_related('author').select_related(
'previouspost').select_related('nextpost').annotate(
rank=search_rank + trigram_similarity).order_by('-rank'))
def search_queryset(queryset, search_fields, search_terms, min_rank):
full_text_vector = sum(
itertools.zip_longest(search_fields, (), fillvalue=Value(' ')), ())
if len(search_fields) > 1:
full_text_vector = full_text_vector[:-1]
full_text_expr = Concat(*full_text_vector, output_field=TextField())
similarity = TrigramSimilarity(full_text_expr, search_terms)
queryset = queryset.annotate(rank=similarity)
if min_rank is None:
queryset = queryset.filter(rank__gt=0.0)
elif min_rank > 0.0:
queryset = queryset.filter(rank__gte=min_rank)
return queryset
def search(self, search_text):
search_vectors = (
SearchVector('article_abstract', weight='A', config='english') +
SearchVector(
StringAgg('keyword_list', delimiter=';'),
weight='B',
config='english',
))
search_query = SearchQuery(search_text, config='english')
search_rank = SearchRank(search_vectors, search_query)
trigram_similarity = TrigramSimilarity('article_abstract', search_text)
qs = (self.get_queryset().filter(search_vector=search_query).annotate(
rank=search_rank + trigram_similarity).order_by('-rank'))
return qs
def search(phrase):
"""Return matching skills for storefront views.
Fuzzy storefront search that is resistant to small typing errors made
by user. Name is matched using trigram similarity, description uses
standard postgres full text search.
Args:
phrase (str): searched phrase
"""
name_sim = TrigramSimilarity('name', phrase)
published = Q(is_published=True)
ft_in_description = Q(description__search=phrase)
name_similar = Q(name_sim__gt=0.2)
return Skill.objects.annotate(
name_sim=name_sim).filter((ft_in_description | name_similar)
& published)
def keyword_search_view(request):
if not request.user.is_authenticated or request.user.is_banned:
return redirect('board:unpermitted')
if request.GET.get('keyword'):
keyword = request.GET['keyword']
else:
keyword = ''
user_vector = TrigramSimilarity(
'username', keyword
) + TrigramSimilarity(
'email', keyword
)
thread_vector = TrigramSimilarity(
'title', keyword
) + TrigramSimilarity(
'content', keyword
) + TrigramSimilarity(
'user__username', keyword
) + TrigramSimilarity(
'tags', keyword
)
post_vector = TrigramSimilarity(
'content', keyword
)
matched_users = EmailUser.objects.annotate(
similarity=user_vector,
).filter(similarity__gt=0.3).order_by('-similarity')
matched_threads = Thread.objects.annotate(
similarity=thread_vector,
).filter(similarity__gt=0.15).order_by('-similarity')
matched_posts = Post.objects.annotate(
similarity=post_vector,
).filter(similarity__gt=0.15).order_by('-similarity')
context = {
'matched_users': matched_users,
'matched_threads': matched_threads,
'matched_posts': matched_posts,
'keyword': keyword
}
return render(
request,
'board/themes/{}/keyword_search.html'.format(BOARD_THEME),
context
)
def get_title_results(query, sections=None):
filter_keyword_args = {}
if sections is not None:
filter_keyword_args["sections__in"] = [
section.id for section in sections
]
trigram_results = StyleGuideEntry.objects.annotate(
similarity=TrigramSimilarity(
'title', query), ).filter(**filter_keyword_args).filter(
similarity__gt=0.4).order_by('-similarity')
starts_with_results = StyleGuideEntry.objects.filter(
**filter_keyword_args).filter(title__istartswith=query)
results = OrderedDuplicateFreeList()
results.append(trigram_results)
results.append(starts_with_results)
return results
def get(self, request, format=None):
form = SearchForm()
results = []
query = None
if request.GET.get('query', None):
form = SearchForm(request.GET)
if form.is_valid():
query = form.cleaned_data['query']
search_vector = SearchVector('title', 'body')
search_query = SearchQuery(query)
results = Post.objects.annotate(
similarity=TrigramSimilarity('title', query),
).filter(similarity__gt=0.1).order_by('-similarity')
return render(request,
'blog/post/search.html',
{'form': form,
'query': query,
'results': results})
def search_results(request, slug):
context = dict()
try:
user = Account.objects.get(pk=request.session['user'])
context['user'] = user
except:
return redirect(reverse(accountsviews.index))
else:
pass
try:
results = City.objects.annotate(similarity=TrigramSimilarity(
'name', slug), ).filter(similarity__gt=0).order_by('-similarity')
context['results_similar'] = results
matching_cities = results.values('name')
other_cities = City.objects.exclude(name__in=matching_cities)
context['other_cities'] = other_cities
except:
pass
return render(request, 'app/city/search_results.html', context)
def post_search(request):
query=None
context=None
results=None
trigram_results=None
result=None
form=SearchForm()
if 'query' in request.GET:
form=SearchForm(request.GET)
if form.is_valid():
query=form.cleaned_data['query']
print(query)
search_vector = SearchVector('title', 'body')
search_query = SearchQuery(query)
result=Post.published.annotate(search=search_vector,rank=SearchRank(search_vector,search_query)).filter(search=search_query).order_by('-rank')
trigram_results=Post.published.annotate(similarity=TrigramSimilarity('title',query)).filter(similarity__gt=0.3).order_by('-similarity')
context={'form':form,'results':trigram_results,'result':result,'query':query}
template='blog/search.html'
return render(request,template,context)
def search(self, search_text):
search_vectors = (
SearchVector("text_plain", weight="A", config="english")
+ SearchVector("text_html", weight="A", config="english")
+ SearchVector("subject", weight="B", config="english")
)
search_query = SearchQuery(search_text)
search_rank = SearchRank(search_vectors, search_query)
subject_tr_si = TrigramSimilarity("subject", search_text)
qs = (
self.get_queryset()
.filter(search_vector=search_query)
.annotate(
rank=search_rank,
similarity=subject_tr_si,
)
.order_by("-rank")
)
return qs
def post_search(request):
"""
The query is actually "not that good" for my own conclusion.
The new (check it by `git log`) one
is for 'put the more-relevant posts at first' (for search-result)
Two old changes
0) search_vector
SearchVector('title', 'body')
SearchVector('title', weight='A') + SearchVector('body', weight='B')
1) results
filter(search=search_query).order_by('-rank')
filter(rank__gte=0.3).order_by('-rank')
"""
form = SearchForm()
query = None
results = []
if 'query' in request.GET:
form = SearchForm(request.GET)
if form.is_valid():
query = form.cleaned_data['query']
search_vector = SearchVector('title', weight='A') \
+ SearchVector('body', weight='B')
search_query = SearchQuery(query)
results = Post.objects.annotate(similarity=TrigramSimilarity(
'title',
query), ).filter(similarity__gt=0.3).order_by('-similarity')
return render(request, 'blog/post/search.html', {
'form': form,
'query': query,
'results': results
})
def get_queryset(self):
self.queryset = self.queryset.filter(
space=self.request.space).order_by('name')
query = self.request.query_params.get('query', None)
fuzzy = self.request.user.searchpreference.lookup
if query is not None and query not in ["''", '']:
if fuzzy:
self.queryset = (
self.queryset.annotate(
exact=Case(When(name__iexact=query, then=(Value(100))),
default=Value(0))
) # put exact matches at the top of the result set
.annotate(trigram=TrigramSimilarity('name', query)).filter(
trigram__gt=0.2).order_by('-exact', '-trigram'))
else:
# TODO have this check unaccent search settings or other search preferences?
self.queryset = (
self.queryset.annotate(
exact=Case(When(name__iexact=query, then=(Value(100))),
default=Value(0))
) # put exact matches at the top of the result set
.filter(name__icontains=query).order_by('-exact', 'name'))
updated_at = self.request.query_params.get('updated_at', None)
if updated_at is not None:
try:
self.queryset = self.queryset.filter(
updated_at__gte=updated_at)
except FieldError:
pass
except ValidationError:
raise APIException(
_('Parameter updated_at incorrectly formatted'))
limit = self.request.query_params.get('limit', None)
random = self.request.query_params.get('random', False)
if limit is not None:
if random:
self.queryset = self.queryset.order_by("?")
self.queryset = self.queryset[:int(limit)]
return self.queryset
