4

I have an autocomplete box which needs to return results having input words. However the input words can be partial and located in different order or places.

Example:

The values in database column (MySQL)-

Expected quarterly sales
Sales preceding quarter
Profit preceding quarter
Sales 12 months

Now if user types quarter sales then it should return both of the first two results.

I tried:

column__icontains = term  #searches only '%quarter sales% and thus gives no results
column__search = term  #searches any of complete words and also returns last result
**{'ratio_name__icontains':each_term for each_term in term.split()}   #this searches only sales as it is the last keyword argument

Any trick via regex or may be something I am missing inbuilt in Django since this is a common pattern?

Pratyush
  • 5,108
  • 6
  • 41
  • 63
  • 1
    Maybe I'm mistaken, but this looks more like a Full-Text search problem to me. You should take a look at MySQL Full-Text search and see if it fits your needs. – Manoj Govindan May 13 '12 at 07:17
  • 1
    Did you try to use any search engine? Check haystack (http://haystacksearch.org/) out. It supports whoosh which is very easy to use at the beginning, and much more – szaman May 13 '12 at 07:45
  • Hi Manoj, I did try full-text search but it returns results which have ANY of the above words. Example it also returns Sales 12 months (which it shouldn't). – Pratyush May 13 '12 at 07:45

2 Answers2

9

Search engines are better for this task, but you can still do it with basic code. If you're looking for strings containing "A" and "B", you can

Model.objects.filter(string__contains='A').filter(string__contains='B')

or

Model.objects.filter(Q(string__contains='A') & Q(string__contains='B'))

But really, you'd be better going with a simple full text search engine with little configuration, like Haystack/Whoosh

Steve K
  • 10,879
  • 4
  • 39
  • 39
  • If you don't want to set up a full text search engine, databases now come with built-in full-text search feature. [Django currently supports Postgres](https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/search/) and you can [manually add support for other databases](https://stackoverflow.com/a/55985693/1925257) as well. The built-in full-text search feature won't be as good as a dedicated search engine, of course, but it's still far better than chaining filters for each word. – xyres Sep 07 '19 at 15:03
1

The above answers using chained .filter() require entries to match ALL the filters.

For those wanting "inclusive or" or ANY type behaviour, you can use functools.reduce to chain together django's Q operator for a list of search terms:

from functools import reduce

from django.db.models import Q

list_of_search_terms = ["quarter", "sales"]
query = reduce(
    lambda a, b: a | b,
    (Q(column__icontains=term) for term in list_of_search_terms),
)
YourModel.objects.filter(query)
binnev
  • 1,658
  • 1
  • 14
  • 16