Difference between Django's __search vs. __icontains

Question

I've started learning Django very recently, so please forgive me if this is too newbie a question for you.

I have a search function implemented in Django. I created in MySQL DB full-text-index and then have a field in my, say, Book model: search_keywords, where I basically store all the words including the title and the content of the book. Then my search function does something similar to this:

search_keyword = self.request.GET.get('keywords')
    if search_keyword:
        # '*' is added for cases where user enters only part of the keyword
        qs = qs.filter(keywords__search=search_keyword + '*')

But the above approach doesn't work if my search query is 'intro' where I know there's a word/term like 'test.introduction' (if the term is just 'introduction' then it works fine).

Then when I try this instead:

        qs = qs.filter(keywords__icontains=search_keyword)

it finds 'test.introduction' etc.

So I begin to wonder why this is the case. Is '__search' only for full-word searches? I know the case for '__icontains' makes sense (case-insensitive and contains part of the word), but why take trouble to create full-text-indexes in the DB just so we can use '__search' in Django? Is that the speed advantage (e.g., in cases of huge volumes of texts to search? Or am I missing something completely here?

Dingus

A different is, __search doesn't work with JSON_FIELD – Ephebe

Answer 1

The difference is about the resulting SQL Query to be executed on the database... I personally prefer "__icontains" because is supported for all databases, and "__search" only for mysql (as django docs) (also supporting PostgreSQL in Django ≥ 1.10 — see documentation).

Look at the query for each method:

Using __search

>>> str(Contact.objects.filter(first_name__search='john').query)

'SELECT `contact_contact`.`id`, `contact_contact`.`title`, `contact_contact`.`first_name`, `contact_contact`.`last_name`, `contact_contact`.`user_id`, `contact_contact`.`role_id`, `contact_contact`.`organization_id`, `contact_contact`.`dob`, `contact_contact`.`email`, `contact_contact`.`notes`, `contact_contact`.`create_date` FROM `contact_contact` WHERE MATCH (`contact_contact`.`first_name`) AGAINST (john IN BOOLEAN MODE)'

Using __icontains

>>> str(Contact.objects.filter(first_name__icontains='john').query)

'SELECT `contact_contact`.`id`, `contact_contact`.`title`, `contact_contact`.`first_name`, `contact_contact`.`last_name`, `contact_contact`.`user_id`, `contact_contact`.`role_id`, `contact_contact`.`organization_id`, `contact_contact`.`dob`, `contact_contact`.`email`, `contact_contact`.`notes`, `contact_contact`.`create_date` FROM `contact_contact` WHERE `contact_contact`.`first_name` LIKE %john% '

Buonomo

Thanks, @Darwin! That explains a lot. I also learned how to check the underlying queries of Django from you. :) – Dingus

This can be a little miss leading...as __icontains is not considered to be a full text search. – Thurgau

At least for PostgreSQL there is a NotImplementedError if I try __search and the complete error message is "Full-text search is not implemented for this database backend". Django version 1.8 – Inkster

@Dingus Is there an easy way to check the underlying queries that Django is running ? – Louanne

@Louanne Sorry, I am also fairly new to Django, so I do not know the answer to your question. Hope you find it somewhere. :) – Dingus

@Dingus Ohh...I thought you said in this comment that you learnt it from Darwin #22800419 – Louanne

@akki, Ah...I see what you mean! What I said to him was that I learned to see the equivalent SQL statement that Django creates in the background for us by doing str(YourModelName.objects.filter(attributeName__search='something').query). That's pretty much all. You can see it in Darwin's example above, too. Hope that helps?? – Dingus

Don't know how I missed that part of the answer. Thanks :) – Louanne

Answer 2

as I tried, __search works just for whole word within multiple words (Incredible!), for example when my Book model contains an object with title='The little prance', while

Country.objects.filter(name__contais='itt')

returns one object,

Country.objects.filter(name__search='itt')

return nothing!

Dareen

This might be related to the underlying algorithm. search uses tsvector. – Hamite

Answer 3

Search has the advantage of allowing search for multiple fields:

>>> Entry.objects.annotate(
...     search=SearchVector('blog__tagline', 'body_text'),
... ).filter(search='cheese')
[
    <Entry: Cheese on Toast recipes>,
    <Entry: Pizza Recipes>,
    <Entry: Dairy farming in Argentina>,
]

Hamite